﻿using AutoMapper;
using Dapper;
using DocumentFormat.OpenXml.Bibliography;
using DocumentFormat.OpenXml.Math;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Wordprocessing;
using Hangfire.LiteDB;
using housingsafety.server.Caching.Core;
using housingsafety.server.Entities;
using housingsafety.server.Entities.DataModels;
using housingsafety.server.FastDBF;
using housingsafety.server.Helpers;
using housingsafety.server.Models;
using housingsafety.server.Repository;
using housingsafety.server.Repository.Extensions;
using housingsafety.server.Svcs;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.WebUtilities;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Microsoft.Net.Http.Headers;
using MySql.Data.MySqlClient;
using NetTopologySuite.Geometries;
using NetTopologySuite.IO;
using Newtonsoft.Json;
using Npgsql;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using Quartz.Util;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Drawing.Printing;
using System.IO;
using System.Linq;
using System.Net;
using System.Reflection;
using System.Text;
using System.Text.Json;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Transactions;
using static housingsafety.server.Controllers.VillageMngController;

namespace housingsafety.server.Controllers
{
    /// <summary>
    /// 村庄管理服务
    /// </summary>
    public class SpecialCampaignController : BaseController<VillageMngController>
    {
        private readonly ICache _cache;
        private readonly IMapper _mapper;
        private readonly IStorageProvider _storageProvider;
        private readonly ZmDbContext _dbContext;
        private readonly IRepository<ZmUser> _userRepository;
        private readonly IRepository<ZmBuildingsLayer> _buildingsRepository;
        private readonly IRepository<ZmFileAttach> _fileAttachRepository;
        private readonly IRepository<ZmVillageFireSafety> _villageFireSafetyRepository;

        public SpecialCampaignController(IStorageProvider storageProvider
            , IRepository<ZmUser> userRepository
            , IRepository<ZmBuildingsLayer> buildingsRepository
            , IRepository<ZmFileAttach> fileAttachRepository
            , IRepository<ZmVillageFireSafety> villageFireSafetyRepository
            , IMapper mapper, ICacheProvider cacheProvider, ZmDbContext dbContext
            , ILogger<VillageMngController> logger
            , IRepository<ZmSystemLog> systemLogRepository) : base(logger, systemLogRepository)
        {
            _storageProvider = storageProvider;
            _mapper = mapper;
            _dbContext = dbContext;
            _cache = cacheProvider.CreateCache();

            _userRepository = userRepository;
            _buildingsRepository = buildingsRepository;
            _fileAttachRepository = fileAttachRepository;
            _villageFireSafetyRepository = villageFireSafetyRepository;
        }

        /// <summary>
        /// 根据行政区划统计双通道情况
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("targeted/HouseStdList")]
        public RespRet<Page<object>> baseInfoDetaile([FromQuery] int pageIndex, [FromQuery] int pageSize, [FromQuery] string startTime, [FromQuery] string endTime, [FromQuery] string cked, [FromQuery] string roletype)
        {
            if (pageIndex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }
            string district = user.District;
            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);

            string strWhere = "";
            if (model != null)
            {
                if (user.RoleType == 1 || user.RoleType == 2)
                {
                    strWhere += "and  xzq!='东城区' and xzq!='西城区'";
                }
                else if (user.RoleType == 3) //区级
                {
                    strWhere += " and xzqdm='" + model.RegionCode + "'";
                }
                else  //乡镇
                {
                    strWhere += "and xzqdm='" + model.RegionCode.Substring(0, 6) + "' ";
                }
            }
            if (string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime))
            {
                startTime = "" + startTime + "";
                endTime = DateTime.Now.ToString("yyyy-MM-dd");

            }
            string today = DateTime.Now.ToString("yyyy-MM-dd");
            string table_fwzt = "";
            if (!string.IsNullOrEmpty(endTime))
            {
                DateTime endData = Convert.ToDateTime(endTime);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            else
            {
                DateTime endData = Convert.ToDateTime(today);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            string table_baselog = "";
            string table_yhlog = "";
            string yhly = "";
            if (roletype == "2" || roletype == "3" || roletype == "4")
            {
                table_baselog = "zm_pc_baselog_sqjz";
                table_yhlog = "zm_pc_yhlog_sqjz";
                yhly = roletype;
            }
            else if (roletype == "5") 
            {
                table_baselog = "zm_pc_baselog_cj";
                table_yhlog = "zm_pc_yhlog_cj";
            }
            string sql = "  select ROW_NUMBER() OVER()index ,* from(SELECT t1.* FROM pro_std_gq('" + startTime+"', '"+endTime+" 23:59:59', '"+ yhly + "','"+table_baselog+"','"+table_yhlog+"','"+ table_fwzt + "')t1 left join zm_dic_xzq t2 on t1.xzqdm=t2.xzqdm order by t2.\"orderIdx\" ) t where 1=1 " + strWhere;
            string sql_count = " select count(1) cnt from(SELECT * FROM pro_std_gq('" + startTime + "', '" + endTime + " 23:59:59', '"+ yhly + "','" + table_baselog + "','" + table_yhlog + "','" + table_fwzt + "') order by xzqdm) t where 1=1 " + strWhere;

            if (roletype == "6")
            {
                sql = "  select ROW_NUMBER() OVER()index ,* from(SELECT * FROM pro_std_gq_zc('" + endTime + " 23:59:59','" + table_fwzt + "') order by xzqdm) t where 1=1";
                sql_count = " select count(1) cnt from(SELECT * FROM pro_std_gq_zc('" + endTime + " 23:59:59','" + table_fwzt + "') order by xzqdm) t where 1=1 " + strWhere;

            }



            if (cked == "true")
            {
                sql = @"select ROW_NUMBER() OVER()index ,*  from (select *,CASE WHEN one_ywccount = 0 THEN '0.00%' ELSE concat(CASE WHEN (round(((one_ywccount)::NUMERIC/(one)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00 ELSE round(((one_ywccount)::NUMERIC/(one)::NUMERIC)*(100)::NUMERIC, 2)  END,'%')  END one_pencent,
        CASE WHEN two_ywccount = 0 THEN '0.00%' ELSE concat( CASE WHEN (round(((two_ywccount)::NUMERIC/(two)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00  ELSE round(((two_ywccount)::NUMERIC/(two)::NUMERIC)*(100)::NUMERIC, 2)  END, '%' )  END two_pencent,
        CASE WHEN three_ywccount = 0 THEN '0.00%'  ELSE concat(CASE WHEN (round(((three_ywccount)::NUMERIC/(three)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00  ELSE round(((three_ywccount)::NUMERIC/(three)::NUMERIC)*(100)::NUMERIC, 2)  END, '%' )  END three_pencent,
				CASE WHEN ssltdbfjsl = 0 THEN '0.00%' ELSE CONCAT(CASE	WHEN (ROUND((CASE	WHEN (zccount - ssltdbfjsl) < 0 THEN 0 ELSE zccount - ssltdbfjsl END)::NUMERIC / zccount::NUMERIC * 100, 2) > 100) THEN 100.00 ELSE ROUND((	CASE WHEN (zccount - ssltdbfjsl) < 0 THEN 0 	ELSE zccount - ssltdbfjsl 	END	)::NUMERIC / zccount::NUMERIC * 100, 2) END,'%' ) END AS wdbl_percent,
			  CASE WHEN ssltdbfjsl = 0 THEN '0.00%' ELSE concat( CASE WHEN (round(((ssltdbfjsl)::NUMERIC/(zccount)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00   ELSE round(((ssltdbfjsl)::NUMERIC/(zccount)::NUMERIC)*(100)::NUMERIC, 2)  END, '%' ) END dbl_pencent,
			CASE WHEN first_no_count = 0 THEN '0.00%'  ELSE concat( CASE WHEN (round(((second_yes_count)::NUMERIC/(first_no_count)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00  ELSE round(((second_yes_count)::NUMERIC/(first_no_count)::NUMERIC)*(100)::NUMERIC, 2)END, '%' ) END zgl_pencent,	
CASE WHEN one_ywccount = 0 THEN '0.00%'  ELSE concat( CASE WHEN (round(((zccount)::NUMERIC/(zcount)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00  ELSE round(((zccount)::NUMERIC/(zcount)::NUMERIC)*(100)::NUMERIC, 2)   END,'%') 
        END hy_pencent from(SELECT xzq,xzqdm,sum(zcount) zcount,sum(zccount) zccount,sum(ssltdbfjsl) ssltdbfjsl,sum(first_no_count) first_no_count,sum(second_yes_count) second_yes_count,sum(wwccount) wwccount,sum(one) one,sum(two) two,sum(three) three ,sum(one_ywccount) one_ywccount,
sum(two_ywccount) two_ywccount,sum(three_ywccount) three_ywccount FROM pro_std_cj('" + startTime + "', '" + endTime + " 23:59:59', '" + yhly + "','" + table_baselog + "','" + table_yhlog + "','"+ table_fwzt + "') where  villagedm in(select villagedm from zdcmd ) group by xzq,xzqdm order by xzqdm) t where 1=1 ) t where 1=1  " + strWhere;

                sql_count = @"select count(1) cnt from (select *,CASE WHEN one_ywccount = 0 THEN '0.00%' ELSE concat(CASE WHEN (round(((one_ywccount)::NUMERIC/(one)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00 ELSE round(((one_ywccount)::NUMERIC/(one)::NUMERIC)*(100)::NUMERIC, 2)  END,'%')  END one_pencent,
        CASE WHEN two_ywccount = 0 THEN '0.00%' ELSE concat( CASE WHEN (round(((two_ywccount)::NUMERIC/(two)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00  ELSE round(((two_ywccount)::NUMERIC/(two)::NUMERIC)*(100)::NUMERIC, 2)  END, '%' )  END two_pencent,
        CASE WHEN three_ywccount = 0 THEN '0.00%'  ELSE concat(CASE WHEN (round(((three_ywccount)::NUMERIC/(three)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00  ELSE round(((three_ywccount)::NUMERIC/(three)::NUMERIC)*(100)::NUMERIC, 2)  END, '%' )  END three_pencent,
				CASE WHEN ssltdbfjsl = 0 THEN '0.00%' ELSE CONCAT(CASE	WHEN (ROUND((CASE	WHEN (zccount - ssltdbfjsl) < 0 THEN 0 ELSE zccount - ssltdbfjsl END)::NUMERIC / zccount::NUMERIC * 100, 2) > 100) THEN 100.00 ELSE ROUND((	CASE WHEN (zccount - ssltdbfjsl) < 0 THEN 0 	ELSE zccount - ssltdbfjsl 	END	)::NUMERIC / zccount::NUMERIC * 100, 2) END,'%' ) END AS wdbl_percent,
			  CASE WHEN ssltdbfjsl = 0 THEN '0.00%' ELSE concat( CASE WHEN (round(((ssltdbfjsl)::NUMERIC/(zccount)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00   ELSE round(((ssltdbfjsl)::NUMERIC/(zccount)::NUMERIC)*(100)::NUMERIC, 2)  END, '%' ) END dbl_pencent,
			CASE WHEN first_no_count = 0 THEN '0.00%'  ELSE concat( CASE WHEN (round(((second_yes_count)::NUMERIC/(first_no_count)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00  ELSE round(((second_yes_count)::NUMERIC/(first_no_count)::NUMERIC)*(100)::NUMERIC, 2)END, '%' ) END zgl_pencent,	
CASE WHEN one_ywccount = 0 THEN '0.00%'  ELSE concat( CASE WHEN (round(((zccount)::NUMERIC/(zcount)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00  ELSE round(((zccount)::NUMERIC/(zcount)::NUMERIC)*(100)::NUMERIC, 2)   END,'%') 
        END hy_pencent from(SELECT xzq,xzqdm,sum(zcount) zcount,sum(zccount) zccount,sum(ssltdbfjsl) ssltdbfjsl,sum(first_no_count) first_no_count,sum(second_yes_count) second_yes_count,sum(wwccount) wwccount,sum(one) one,sum(two) two,sum(three) three ,sum(one_ywccount) one_ywccount,
sum(two_ywccount) two_ywccount,sum(three_ywccount) three_ywccount FROM pro_std_cj('" + startTime + "', '" + endTime + " 23:59:59', '" + yhly + "','" + table_baselog + "','" + table_yhlog + "','"+ table_fwzt + "') where  villagedm in(select villagedm from zdcmd ) group by xzq,xzqdm order by xzqdm) t where 1=1) t where  1=1 " + strWhere;
                if (roletype == "6")
                {
                    sql = @"select ROW_NUMBER() OVER()index ,*  from (select *,CASE WHEN one_ywccount = 0 THEN '0.00%' ELSE concat(CASE WHEN (round(((one_ywccount)::NUMERIC/(one)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00 ELSE round(((one_ywccount)::NUMERIC/(one)::NUMERIC)*(100)::NUMERIC, 2)  END,'%')  END one_pencent,
        CASE WHEN two_ywccount = 0 THEN '0.00%' ELSE concat( CASE WHEN (round(((two_ywccount)::NUMERIC/(two)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00  ELSE round(((two_ywccount)::NUMERIC/(two)::NUMERIC)*(100)::NUMERIC, 2)  END, '%' )  END two_pencent,
        CASE WHEN three_ywccount = 0 THEN '0.00%'  ELSE concat(CASE WHEN (round(((three_ywccount)::NUMERIC/(three)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00  ELSE round(((three_ywccount)::NUMERIC/(three)::NUMERIC)*(100)::NUMERIC, 2)  END, '%' )  END three_pencent,
				CASE WHEN ssltdbfjsl = 0 THEN '0.00%' ELSE CONCAT(CASE	WHEN (ROUND((CASE	WHEN (zccount - ssltdbfjsl) < 0 THEN 0 ELSE zccount - ssltdbfjsl END)::NUMERIC / zccount::NUMERIC * 100, 2) > 100) THEN 100.00 ELSE ROUND((	CASE WHEN (zccount - ssltdbfjsl) < 0 THEN 0 	ELSE zccount - ssltdbfjsl 	END	)::NUMERIC / zccount::NUMERIC * 100, 2) END,'%' ) END AS wdbl_percent,
			  CASE WHEN ssltdbfjsl = 0 THEN '0.00%' ELSE concat( CASE WHEN (round(((ssltdbfjsl)::NUMERIC/(zccount)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00   ELSE round(((ssltdbfjsl)::NUMERIC/(zccount)::NUMERIC)*(100)::NUMERIC, 2)  END, '%' ) END dbl_pencent,
			CASE WHEN first_no_count = 0 THEN '0.00%'  ELSE concat( CASE WHEN (round(((second_yes_count)::NUMERIC/(first_no_count)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00  ELSE round(((second_yes_count)::NUMERIC/(first_no_count)::NUMERIC)*(100)::NUMERIC, 2)END, '%' ) END zgl_pencent,	
CASE WHEN one_ywccount = 0 THEN '0.00%'  ELSE concat( CASE WHEN (round(((zccount)::NUMERIC/(zcount)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00  ELSE round(((zccount)::NUMERIC/(zcount)::NUMERIC)*(100)::NUMERIC, 2)   END,'%') 
        END hy_pencent from(SELECT xzq,xzqdm,sum(zcount) zcount,sum(zccount) zccount,sum(ssltdbfjsl) ssltdbfjsl,sum(first_no_count) first_no_count,sum(second_yes_count) second_yes_count,sum(wwccount) wwccount,sum(one) one,sum(two) two,sum(three) three ,sum(one_ywccount) one_ywccount,
sum(two_ywccount) two_ywccount,sum(three_ywccount) three_ywccount FROM pro_std_cj_zc( '" + endTime + " 23:59:59','" + table_fwzt + "') where  villagedm in(select villagedm from zdcmd ) group by xzq,xzqdm order by xzqdm) t where 1=1 ) t where 1=1  " + strWhere;

                    sql_count = @"select count(1) cnt from (select *,CASE WHEN one_ywccount = 0 THEN '0.00%' ELSE concat(CASE WHEN (round(((one_ywccount)::NUMERIC/(one)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00 ELSE round(((one_ywccount)::NUMERIC/(one)::NUMERIC)*(100)::NUMERIC, 2)  END,'%')  END one_pencent,
        CASE WHEN two_ywccount = 0 THEN '0.00%' ELSE concat( CASE WHEN (round(((two_ywccount)::NUMERIC/(two)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00  ELSE round(((two_ywccount)::NUMERIC/(two)::NUMERIC)*(100)::NUMERIC, 2)  END, '%' )  END two_pencent,
        CASE WHEN three_ywccount = 0 THEN '0.00%'  ELSE concat(CASE WHEN (round(((three_ywccount)::NUMERIC/(three)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00  ELSE round(((three_ywccount)::NUMERIC/(three)::NUMERIC)*(100)::NUMERIC, 2)  END, '%' )  END three_pencent,
				CASE WHEN ssltdbfjsl = 0 THEN '0.00%' ELSE CONCAT(CASE	WHEN (ROUND((CASE	WHEN (zccount - ssltdbfjsl) < 0 THEN 0 ELSE zccount - ssltdbfjsl END)::NUMERIC / zccount::NUMERIC * 100, 2) > 100) THEN 100.00 ELSE ROUND((	CASE WHEN (zccount - ssltdbfjsl) < 0 THEN 0 	ELSE zccount - ssltdbfjsl 	END	)::NUMERIC / zccount::NUMERIC * 100, 2) END,'%' ) END AS wdbl_percent,
			  CASE WHEN ssltdbfjsl = 0 THEN '0.00%' ELSE concat( CASE WHEN (round(((ssltdbfjsl)::NUMERIC/(zccount)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00   ELSE round(((ssltdbfjsl)::NUMERIC/(zccount)::NUMERIC)*(100)::NUMERIC, 2)  END, '%' ) END dbl_pencent,
			CASE WHEN first_no_count = 0 THEN '0.00%'  ELSE concat( CASE WHEN (round(((second_yes_count)::NUMERIC/(first_no_count)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00  ELSE round(((second_yes_count)::NUMERIC/(first_no_count)::NUMERIC)*(100)::NUMERIC, 2)END, '%' ) END zgl_pencent,	
CASE WHEN one_ywccount = 0 THEN '0.00%'  ELSE concat( CASE WHEN (round(((zccount)::NUMERIC/(zcount)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00  ELSE round(((zccount)::NUMERIC/(zcount)::NUMERIC)*(100)::NUMERIC, 2)   END,'%') 
        END hy_pencent from(SELECT xzq,xzqdm,sum(zcount) zcount,sum(zccount) zccount,sum(ssltdbfjsl) ssltdbfjsl,sum(first_no_count) first_no_count,sum(second_yes_count) second_yes_count,sum(wwccount) wwccount,sum(one) one,sum(two) two,sum(three) three ,sum(one_ywccount) one_ywccount,
sum(two_ywccount) two_ywccount,sum(three_ywccount) three_ywccount FROM pro_std_cj_zc( '" + endTime + " 23:59:59','" + table_fwzt + "') where  villagedm in(select villagedm from zdcmd ) group by xzq,xzqdm order by xzqdm) t where 1=1) t where  1=1 " + strWhere;
                }

            }

            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
      || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
      || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }

            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
          || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
          || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            //执行语句
            var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);

            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = list, NextPageIndex = pageIndex, PageTotal = count, Total = count });

        }
        /// <summary>
        /// 根据乡镇统计双通道情况
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("targeted/HouseTownStdList")]
        public RespRet<Page<object>> HouseTownStdList([FromQuery] int pageIndex, [FromQuery] int pageSize, [FromQuery] string startTime, [FromQuery] string endTime,  [FromQuery] string roletype, [FromQuery] string xzqdm, [FromQuery] string cked)
        {
            if (pageIndex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }
            string district = user.District;
            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);

            string strWhere = "";
            if (model != null)
            {
                if (user.RoleType == 1 || user.RoleType == 2)
                {
                    strWhere += "and  xzq!='东城区' and xzq!='西城区'";
                }
                else if (user.RoleType == 3) //区级
                {
                    strWhere += " and xzqdm='" + model.RegionCode + "'";
                }
                else  //乡镇
                {
                    strWhere += "and xzqdm='" + model.RegionCode.Substring(0, 6) + "' ";
                }
            }
            if (string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime))
            {
                startTime = "" + startTime + "";
                endTime = DateTime.Now.ToString("yyyy-MM-dd");
            }
            if (!string.IsNullOrEmpty(xzqdm)) 
            {
                strWhere += " and xzqdm='" + xzqdm + "'";
            }
            string table_baselog = "";
            string table_yhlog = "";
            string yhly = "";
            if (roletype == "2" || roletype == "3" || roletype == "4")
            {
                table_baselog = "zm_pc_baselog_sqjz";
                table_yhlog = "zm_pc_yhlog_sqjz";
                yhly = roletype;
            }
            else if (roletype == "5")
            {
                table_baselog = "zm_pc_baselog_cj";
                table_yhlog = "zm_pc_yhlog_cj";
            }
            else if (roletype == "6")
            {
                table_baselog = "zm_pc_baselog";
                table_yhlog = "zm_pc_yhlog";
            }
            string today = DateTime.Now.ToString("yyyy-MM-dd");
            string table_fwzt = "";
            if (!string.IsNullOrEmpty(endTime))
            {
                DateTime endData = Convert.ToDateTime(endTime);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            else
            {
                DateTime endData = Convert.ToDateTime(today);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            string sql = "  select ROW_NUMBER() OVER()index ,* from(SELECT * FROM pro_std_xz('" + startTime + "', '" + endTime + " 23:59:59', '"+ yhly + "','" + table_baselog + "','" + table_yhlog + "','"+ table_fwzt + "') where 1=1 "+ strWhere + " order by xzqdm) t where 1=1";

            string sql_count = " select count(1) cnt from(SELECT * FROM pro_std_xz('" + startTime + "', '" + endTime + " 23:59:59', '2','" + table_baselog + "','" + table_yhlog + "','"+ table_fwzt + "') order by xzqdm) t where 1=1 " + strWhere;
            if (roletype == "6")
            {
                sql = "  select ROW_NUMBER() OVER()index ,* from(SELECT * FROM pro_std_xz_zc('" + endTime + " 23:59:59','" + table_fwzt + "') order by xzqdm) t where 1=1";
                sql_count = " select count(1) cnt from(SELECT * FROM pro_std_xz_zc('" + endTime + " 23:59:59','" + table_fwzt + "') order by xzqdm) t where 1=1 " + strWhere;

            }
            if (cked == "true")
            {
                sql = @"select ROW_NUMBER() OVER()index ,*  from (select *,CASE WHEN one_ywccount = 0 THEN '0.00%' ELSE concat(CASE WHEN (round(((one_ywccount)::NUMERIC/(one)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00 ELSE round(((one_ywccount)::NUMERIC/(one)::NUMERIC)*(100)::NUMERIC, 2)  END,'%')  END one_pencent,
        CASE WHEN two_ywccount = 0 THEN '0.00%' ELSE concat( CASE WHEN (round(((two_ywccount)::NUMERIC/(two)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00  ELSE round(((two_ywccount)::NUMERIC/(two)::NUMERIC)*(100)::NUMERIC, 2)  END, '%' )  END two_pencent,
        CASE WHEN three_ywccount = 0 THEN '0.00%'  ELSE concat(CASE WHEN (round(((three_ywccount)::NUMERIC/(three)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00  ELSE round(((three_ywccount)::NUMERIC/(three)::NUMERIC)*(100)::NUMERIC, 2)  END, '%' )  END three_pencent,
				CASE WHEN ssltdbfjsl = 0 THEN '0.00%' ELSE CONCAT(CASE	WHEN (ROUND((CASE	WHEN (zccount - ssltdbfjsl) < 0 THEN 0 ELSE zccount - ssltdbfjsl END)::NUMERIC / zccount::NUMERIC * 100, 2) > 100) THEN 100.00 ELSE ROUND((	CASE WHEN (zccount - ssltdbfjsl) < 0 THEN 0 	ELSE zccount - ssltdbfjsl 	END	)::NUMERIC / zccount::NUMERIC * 100, 2) END,'%' ) END AS wdbl_percent,
			  CASE WHEN ssltdbfjsl = 0 THEN '0.00%' ELSE concat( CASE WHEN (round(((ssltdbfjsl)::NUMERIC/(zccount)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00   ELSE round(((ssltdbfjsl)::NUMERIC/(zccount)::NUMERIC)*(100)::NUMERIC, 2)  END, '%' ) END dbl_pencent,
			CASE WHEN first_no_count = 0 THEN '0.00%'  ELSE concat( CASE WHEN (round(((second_yes_count)::NUMERIC/(first_no_count)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00  ELSE round(((second_yes_count)::NUMERIC/(first_no_count)::NUMERIC)*(100)::NUMERIC, 2)END, '%' ) END zgl_pencent,	
CASE WHEN one_ywccount = 0 THEN '0.00%'  ELSE concat( CASE WHEN (round(((zccount)::NUMERIC/(zcount)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00  ELSE round(((zccount)::NUMERIC/(zcount)::NUMERIC)*(100)::NUMERIC, 2)   END,'%') 
        END hy_pencent from(SELECT xzq,xzqdm,town,towndm,sum(zcount) zcount,sum(zccount) zccount,sum(ssltdbfjsl) ssltdbfjsl,sum(first_no_count) first_no_count,sum(second_yes_count) second_yes_count,sum(wwccount) wwccount,sum(one) one,sum(two) two,sum(three) three ,sum(one_ywccount) one_ywccount,
sum(two_ywccount) two_ywccount,sum(three_ywccount) three_ywccount FROM pro_std_cj('" + startTime + "', '" + endTime + " 23:59:59', '" + yhly + "','" + table_baselog + "','" + table_yhlog + "','"+ table_fwzt + "') where  villagedm in(select villagedm from zdcmd ) group by xzq,xzqdm,town,towndm order by xzqdm) t) t where 1=1  " + strWhere;

                sql_count = @"select count(1) cnt  from (select *,CASE WHEN one_ywccount = 0 THEN '0.00%' ELSE concat(CASE WHEN (round(((one_ywccount)::NUMERIC/(one)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00 ELSE round(((one_ywccount)::NUMERIC/(one)::NUMERIC)*(100)::NUMERIC, 2)  END,'%')  END one_pencent,
        CASE WHEN two_ywccount = 0 THEN '0.00%' ELSE concat( CASE WHEN (round(((two_ywccount)::NUMERIC/(two)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00  ELSE round(((two_ywccount)::NUMERIC/(two)::NUMERIC)*(100)::NUMERIC, 2)  END, '%' )  END two_pencent,
        CASE WHEN three_ywccount = 0 THEN '0.00%'  ELSE concat(CASE WHEN (round(((three_ywccount)::NUMERIC/(three)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00  ELSE round(((three_ywccount)::NUMERIC/(three)::NUMERIC)*(100)::NUMERIC, 2)  END, '%' )  END three_pencent,
				CASE WHEN ssltdbfjsl = 0 THEN '0.00%' ELSE CONCAT(CASE	WHEN (ROUND((CASE	WHEN (zccount - ssltdbfjsl) < 0 THEN 0 ELSE zccount - ssltdbfjsl END)::NUMERIC / zccount::NUMERIC * 100, 2) > 100) THEN 100.00 ELSE ROUND((	CASE WHEN (zccount - ssltdbfjsl) < 0 THEN 0 	ELSE zccount - ssltdbfjsl 	END	)::NUMERIC / zccount::NUMERIC * 100, 2) END,'%' ) END AS wdbl_percent,
			  CASE WHEN ssltdbfjsl = 0 THEN '0.00%' ELSE concat( CASE WHEN (round(((ssltdbfjsl)::NUMERIC/(zccount)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00   ELSE round(((ssltdbfjsl)::NUMERIC/(zccount)::NUMERIC)*(100)::NUMERIC, 2)  END, '%' ) END dbl_pencent,
			CASE WHEN first_no_count = 0 THEN '0.00%'  ELSE concat( CASE WHEN (round(((second_yes_count)::NUMERIC/(first_no_count)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00  ELSE round(((second_yes_count)::NUMERIC/(first_no_count)::NUMERIC)*(100)::NUMERIC, 2)END, '%' ) END zgl_pencent,	
CASE WHEN one_ywccount = 0 THEN '0.00%'  ELSE concat( CASE WHEN (round(((zccount)::NUMERIC/(zcount)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00  ELSE round(((zccount)::NUMERIC/(zcount)::NUMERIC)*(100)::NUMERIC, 2)   END,'%') 
        END hy_pencent from(SELECT xzq,xzqdm,town,towndm,sum(zcount) zcount,sum(zccount) zccount,sum(ssltdbfjsl) ssltdbfjsl,sum(first_no_count) first_no_count,sum(second_yes_count) second_yes_count,sum(wwccount) wwccount,sum(one) one,sum(two) two,sum(three) three ,sum(one_ywccount) one_ywccount,
sum(two_ywccount) two_ywccount,sum(three_ywccount) three_ywccount FROM pro_std_cj('" + startTime + "', '" + endTime + " 23:59:59', '" + yhly + "','" + table_baselog + "','" + table_yhlog + "','"+ table_fwzt + "') where  villagedm in(select villagedm from zdcmd ) group by xzq,xzqdm,town,towndm order by xzqdm) t where 1=1 ) t where 1=1 " + strWhere;
                if (roletype == "6") 
                {
                    sql = @"select ROW_NUMBER() OVER()index ,*  from (select *,CASE WHEN one_ywccount = 0 THEN '0.00%' ELSE concat(CASE WHEN (round(((one_ywccount)::NUMERIC/(one)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00 ELSE round(((one_ywccount)::NUMERIC/(one)::NUMERIC)*(100)::NUMERIC, 2)  END,'%')  END one_pencent,
        CASE WHEN two_ywccount = 0 THEN '0.00%' ELSE concat( CASE WHEN (round(((two_ywccount)::NUMERIC/(two)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00  ELSE round(((two_ywccount)::NUMERIC/(two)::NUMERIC)*(100)::NUMERIC, 2)  END, '%' )  END two_pencent,
        CASE WHEN three_ywccount = 0 THEN '0.00%'  ELSE concat(CASE WHEN (round(((three_ywccount)::NUMERIC/(three)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00  ELSE round(((three_ywccount)::NUMERIC/(three)::NUMERIC)*(100)::NUMERIC, 2)  END, '%' )  END three_pencent,
				CASE WHEN ssltdbfjsl = 0 THEN '0.00%' ELSE CONCAT(CASE	WHEN (ROUND((CASE	WHEN (zccount - ssltdbfjsl) < 0 THEN 0 ELSE zccount - ssltdbfjsl END)::NUMERIC / zccount::NUMERIC * 100, 2) > 100) THEN 100.00 ELSE ROUND((	CASE WHEN (zccount - ssltdbfjsl) < 0 THEN 0 	ELSE zccount - ssltdbfjsl 	END	)::NUMERIC / zccount::NUMERIC * 100, 2) END,'%' ) END AS wdbl_percent,
			  CASE WHEN ssltdbfjsl = 0 THEN '0.00%' ELSE concat( CASE WHEN (round(((ssltdbfjsl)::NUMERIC/(zccount)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00   ELSE round(((ssltdbfjsl)::NUMERIC/(zccount)::NUMERIC)*(100)::NUMERIC, 2)  END, '%' ) END dbl_pencent,
			CASE WHEN first_no_count = 0 THEN '0.00%'  ELSE concat( CASE WHEN (round(((second_yes_count)::NUMERIC/(first_no_count)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00  ELSE round(((second_yes_count)::NUMERIC/(first_no_count)::NUMERIC)*(100)::NUMERIC, 2)END, '%' ) END zgl_pencent,	
CASE WHEN one_ywccount = 0 THEN '0.00%'  ELSE concat( CASE WHEN (round(((zccount)::NUMERIC/(zcount)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00  ELSE round(((zccount)::NUMERIC/(zcount)::NUMERIC)*(100)::NUMERIC, 2)   END,'%') 
        END hy_pencent from(SELECT xzq,xzqdm,town,towndm,sum(zcount) zcount,sum(zccount) zccount,sum(ssltdbfjsl) ssltdbfjsl,sum(first_no_count) first_no_count,sum(second_yes_count) second_yes_count,sum(wwccount) wwccount,sum(one) one,sum(two) two,sum(three) three ,sum(one_ywccount) one_ywccount,
sum(two_ywccount) two_ywccount,sum(three_ywccount) three_ywccount FROM pro_std_cj( '" + endTime + " 23:59:59','" + table_fwzt + "') where  villagedm in(select villagedm from zdcmd ) group by xzq,xzqdm,town,towndm order by xzqdm) t) t where 1=1  " + strWhere;

                    sql_count = @"select count(1) cnt  from (select *,CASE WHEN one_ywccount = 0 THEN '0.00%' ELSE concat(CASE WHEN (round(((one_ywccount)::NUMERIC/(one)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00 ELSE round(((one_ywccount)::NUMERIC/(one)::NUMERIC)*(100)::NUMERIC, 2)  END,'%')  END one_pencent,
        CASE WHEN two_ywccount = 0 THEN '0.00%' ELSE concat( CASE WHEN (round(((two_ywccount)::NUMERIC/(two)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00  ELSE round(((two_ywccount)::NUMERIC/(two)::NUMERIC)*(100)::NUMERIC, 2)  END, '%' )  END two_pencent,
        CASE WHEN three_ywccount = 0 THEN '0.00%'  ELSE concat(CASE WHEN (round(((three_ywccount)::NUMERIC/(three)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00  ELSE round(((three_ywccount)::NUMERIC/(three)::NUMERIC)*(100)::NUMERIC, 2)  END, '%' )  END three_pencent,
				CASE WHEN ssltdbfjsl = 0 THEN '0.00%' ELSE CONCAT(CASE	WHEN (ROUND((CASE	WHEN (zccount - ssltdbfjsl) < 0 THEN 0 ELSE zccount - ssltdbfjsl END)::NUMERIC / zccount::NUMERIC * 100, 2) > 100) THEN 100.00 ELSE ROUND((	CASE WHEN (zccount - ssltdbfjsl) < 0 THEN 0 	ELSE zccount - ssltdbfjsl 	END	)::NUMERIC / zccount::NUMERIC * 100, 2) END,'%' ) END AS wdbl_percent,
			  CASE WHEN ssltdbfjsl = 0 THEN '0.00%' ELSE concat( CASE WHEN (round(((ssltdbfjsl)::NUMERIC/(zccount)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00   ELSE round(((ssltdbfjsl)::NUMERIC/(zccount)::NUMERIC)*(100)::NUMERIC, 2)  END, '%' ) END dbl_pencent,
			CASE WHEN first_no_count = 0 THEN '0.00%'  ELSE concat( CASE WHEN (round(((second_yes_count)::NUMERIC/(first_no_count)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00  ELSE round(((second_yes_count)::NUMERIC/(first_no_count)::NUMERIC)*(100)::NUMERIC, 2)END, '%' ) END zgl_pencent,	
CASE WHEN one_ywccount = 0 THEN '0.00%'  ELSE concat( CASE WHEN (round(((zccount)::NUMERIC/(zcount)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00  ELSE round(((zccount)::NUMERIC/(zcount)::NUMERIC)*(100)::NUMERIC, 2)   END,'%') 
        END hy_pencent from(SELECT xzq,xzqdm,town,towndm,sum(zcount) zcount,sum(zccount) zccount,sum(ssltdbfjsl) ssltdbfjsl,sum(first_no_count) first_no_count,sum(second_yes_count) second_yes_count,sum(wwccount) wwccount,sum(one) one,sum(two) two,sum(three) three ,sum(one_ywccount) one_ywccount,
sum(two_ywccount) two_ywccount,sum(three_ywccount) three_ywccount FROM pro_std_cj_zc( '" + endTime + " 23:59:59','" + table_fwzt + "') where  villagedm in(select villagedm from zdcmd ) group by xzq,xzqdm,town,towndm order by xzqdm) t where 1=1 ) t where 1=1 " + strWhere;

                }
            }
            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
      || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
      || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }

            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
          || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
          || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            //执行语句
            var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);

            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = list, NextPageIndex = pageIndex, PageTotal = count, Total = count });

        }
        /// <summary>
        /// 根据村级统计双通道情况
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("targeted/HouseVillageStdList")]
        public RespRet<Page<object>> HouseVillageStdList([FromQuery] int pageIndex, [FromQuery] int pageSize, [FromQuery] string startTime, [FromQuery] string endTime, [FromQuery] string roletype, [FromQuery] string xzqdm, [FromQuery] string towndm,[FromQuery] string cked)
        {
            if (pageIndex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }
            string district = user.District;
            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);

            string strWhere = "";
            if (model != null)
            {
                if (user.RoleType == 1 || user.RoleType == 2)
                {
                    strWhere += "and  xzq!='东城区' and xzq!='西城区'";
                }
                else if (user.RoleType == 3) //区级
                {
                    strWhere += " and xzqdm='" + model.RegionCode + "'";
                }
                else  //乡镇
                {
                    strWhere += "and xzqdm='" + model.RegionCode.Substring(0, 6) + "' ";
                }
            }
            if (string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime))
            {
                startTime = "" + startTime + "";
                endTime = DateTime.Now.ToString("yyyy-MM-dd");
            }
            if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm))
            {
                strWhere += " and xzqdm='" + xzqdm + "' and towndm='" + towndm + "'";
            }
            string table_baselog = "";
            string table_yhlog = "";
            string yhly = "";
            if (roletype == "2" || roletype == "3" || roletype == "4")
            {
                table_baselog = "zm_pc_baselog_sqjz";
                table_yhlog = "zm_pc_yhlog_sqjz";
                yhly = roletype;
            }
            else if (roletype == "5")
            {
                table_baselog = "zm_pc_baselog_cj";
                table_yhlog = "zm_pc_yhlog_cj";
            }
            string today = DateTime.Now.ToString("yyyy-MM-dd");
            string table_fwzt = "";
            if (!string.IsNullOrEmpty(endTime))
            {
                DateTime endData = Convert.ToDateTime(endTime);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            else
            {
                DateTime endData = Convert.ToDateTime(today);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            string sql = "  select ROW_NUMBER() OVER()index ,* from(SELECT * FROM pro_std_cj('" + startTime + "', '" + endTime + " 23:59:59', '"+ yhly + "','" + table_baselog + "','" + table_yhlog + "','"+ table_fwzt + "') order by xzqdm) t where 1=1" + strWhere;

            string sql_count = " select count(1) cnt from(SELECT * FROM pro_std_cj('" + startTime + "', '" + endTime + " 23:59:59', '2','" + table_baselog + "','" + table_yhlog + "','"+ table_fwzt + "') order by xzqdm) t where 1=1 " + strWhere;
            if (roletype == "6")
            {
                sql = "  select ROW_NUMBER() OVER()index ,* from(SELECT * FROM pro_std_cj_zc('" + endTime + " 23:59:59','" + table_fwzt + "') order by xzqdm) t where 1=1";
                sql_count = " select count(1) cnt from(SELECT * FROM pro_std_cj_zc('" + endTime + " 23:59:59','" + table_fwzt + "') order by xzqdm) t where 1=1 " + strWhere;

            }
            if (cked == "true")
            {
                sql = @"select ROW_NUMBER() OVER()index ,* FROM pro_std_cj('" + startTime + "', '" + endTime + " 23:59:59', '" + yhly + "','" + table_baselog + "','" + table_yhlog + "','"+ table_fwzt + "') where  villagedm in(select villagedm from zdcmd )    " + strWhere;

                sql = @"select count(1) cnt FROM pro_std_cj('" + startTime + "', '" + endTime + " 23:59:59', '" + yhly + "','" + table_baselog + "','" + table_yhlog + "','"+ table_fwzt + "') where  villagedm in(select villagedm from zdcmd )  " + strWhere;
                if (roletype == "6")
                {
                    sql = "  select ROW_NUMBER() OVER()index ,* from(SELECT * FROM pro_std_cj_zc('" + endTime + " 23:59:59','" + table_fwzt + "') order by xzqdm) t where 1=1";
                    sql_count = " select count(1) cnt from(SELECT * FROM pro_std_cj_zc('" + endTime + " 23:59:59','" + table_fwzt + "') order by xzqdm) t where 1=1 " + strWhere;

                }
            }
            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
      || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
      || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }

            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
          || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
          || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            //执行语句
            var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);

            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = list, NextPageIndex = pageIndex, PageTotal = count, Total = count });

        }

        /// <summary>
        /// 双通道满足明细（房屋）
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("targeted/std/getHourseByList")]
        public RespRet<Page<object>> getHourseByTypeList([FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] string roletype, [FromQuery] int pageIndex, [FromQuery] int pageSize, [FromQuery] string startTime, [FromQuery] string endTime, [FromQuery] string txtKeyWord, [FromQuery] string nickname, [FromQuery] string floor, [FromQuery] string jsrysstd, [FromQuery] string cked)
        {
            if (pageIndex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }

            var sbSql = new StringBuilder();
            var sbSqlWhere = new StringBuilder();
            var sqlParams = new List<NpgsqlParameter>();

            var sbSqlBuildingEx = new StringBuilder();

            string district = user.District;

            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);



            string strWhere = " and 1=1 ";
            string xzqWhere = "";
            string where = "";
            if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && !string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and t3.xzqdm='" + xzqdm + "' and t3.town='" + towndm + "' and t3.village='" + villagedm + "'";
                xzqWhere = " and t1.xzqdm='" + xzqdm + "' and  t1.town='" + towndm + "' and  t1.village='" + villagedm + "'";
                where = " and xzqdm='" + xzqdm + "' and (town='" + towndm + "') and  (village='" + villagedm + "')";
            }
            else if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and t3.xzqdm='" + xzqdm + "' and t3.town='" + towndm + "' ";
                xzqWhere = " and t1.xzqdm='" + xzqdm + "' and t1.town='" + towndm + "' ";
                where = " and xzqdm='" + xzqdm + "' and (town='" + towndm + "') ";
            }
            else if (!string.IsNullOrEmpty(xzqdm) && string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and t3.xzqdm='" + xzqdm + "' ";
                xzqWhere = " and t1.xzqdm='" + xzqdm + "' ";
                where = " and xzqdm='" + xzqdm + "' ";
            }
            if (!string.IsNullOrEmpty(txtKeyWord))
            {
                where += " and (hzxm like '%" + txtKeyWord + "%' or fwbh like '%" + txtKeyWord + "%' or address like '%" + txtKeyWord + "%')";
            }
            if (!string.IsNullOrEmpty(nickname))
            {
                where += " and (create_name like '%" + nickname + "%')";
            }
            DateTime start = Convert.ToDateTime(startTime);
            DateTime end = Convert.ToDateTime(endTime);
            string toDay = end.ToString("yyyy-MM-dd");
            string table_fwzt = "";
            if (!string.IsNullOrEmpty(endTime))
            {
                DateTime endData = Convert.ToDateTime(endTime);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            else
            {
                DateTime endData = Convert.ToDateTime(toDay);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            string sql = " ";
            string sql_count = " ";
            if (roletype == "6")
            {
                if (jsrysstd == "ljbhg")
                {

                }
                else if (jsrysstd == "zg")
                {

                }
                else
                {

                    sql = @" select ROW_NUMBER() OVER ()index,* from (SELECT t3.* FROM ( SELECT DISTINCT ON (t1.fwbh) t1.ID,t1.xzq,t1.xzqdm,t1.villagedm,t1.towndm,t1.town, t1.address,t1.hzxm,t1.village," +
                     "   t1.cs, t1.jzscxyaqcksl,t1.jzxyssltsl,t1.sstdjsqk,t1.xggdjsbp, t1.fwbh,tdsfdb jsrysstd,t1.create_on  FROM zm_pc_baselog t1  WHERE create_on BETWEEN '" + startTime + "' AND  '" + endTime + " 23:59:59'  " + xzqWhere +
                    "     ORDER BY t1.fwbh, t1.create_on DESC ) t3" +
                   "  INNER JOIN zm_pc_yhlog m2 ON t3.ID = m2.base_id" +
                     " WHERE t3.create_on BETWEEN '" + startTime + "' AND  '" + endTime + " 23:59:59' " + strWhere + " and jsrysstd='" + jsrysstd + "'  " +
                      " AND t3.fwbh not in ( SELECT DISTINCT fwbh FROM cj_fwzt  WHERE row_num = 1   AND (jz_create_on::timestamp <= '" + endTime + " 23:59:59' OR cj_create_on::timestamp <= '" + endTime + " 23:59:59') " +
                     "AND (jz_fwzt IS NOT NULL OR cj_fwzt IS NOT NULL))  order by village)  t  where 1=1  " + where + " limit " + pageSize + " offset(" + (pageIndex - 1) + ") * " + pageSize;
                    sql_count = @" select count(1) cnt from (SELECT t3.* FROM ( SELECT DISTINCT ON (t1.fwbh) t1.ID,t1.xzq,t1.xzqdm,t1.villagedm,t1.towndm,t1.town, t1.address,t1.hzxm,t1.village," +
                      "   t1.cs, t1.jzscxyaqcksl,t1.jzxyssltsl,t1.sstdjsqk,t1.xggdjsbp, t1.fwbh,tdsfdb jsrysstd,t1.create_on  FROM zm_pc_baselog t1  WHERE create_on BETWEEN '" + startTime + "' AND  '" + endTime + " 23:59:59'  " + xzqWhere +
                     "     ORDER BY t1.fwbh, t1.create_on DESC ) t3" +
                    "  INNER JOIN zm_pc_yhlog m2 ON t3.ID = m2.base_id" +
                      " WHERE t3.create_on BETWEEN '" + startTime + "' AND  '" + endTime + " 23:59:59' " + strWhere + " and jsrysstd='" + jsrysstd + "'  " +
                       " AND t3.fwbh not in ( select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) 	and (final_create_on = '" + endTime + "' ) )  order by village)  t  where 1=1  " + where;

                }

            }
            else if (roletype == "5")
            {

                if (jsrysstd == "ljbhg")
                {

                }
                else if (jsrysstd == "zg")
                {

                }
                else
                {
                    sql = @" select ROW_NUMBER() OVER ()index,* from (SELECT t3.* FROM ( SELECT DISTINCT ON (t1.fwbh) t1.ID,t1.xzq,t1.xzqdm,t1.villagedm,t1.towndm,t1.town, t1.address,t1.hzxm,t1.village," +
                  "   t1.cs, t1.jzscxyaqcksl,t1.jzxyssltsl,t1.sstdjsqk,t1.xggdjsbp, t1.fwbh,jsrysstd,t1.create_on,t1.create_name  FROM zm_pc_baselog_cj t1  WHERE create_on BETWEEN '" + startTime + "' AND  '" + endTime + " 23:59:59'  " + xzqWhere +
                 "     ORDER BY t1.fwbh, t1.create_on DESC ) t3" +
                "  INNER JOIN zm_pc_yhlog_cj m2 ON t3.ID = m2.base_id" +
                  " WHERE t3.create_on BETWEEN '" + startTime + "' AND  '" + endTime + " 23:59:59' " + strWhere + " and jsrysstd='" + jsrysstd + "'  " +
                   " AND t3.fwbh not in ( SELECT DISTINCT fwbh FROM cj_fwzt  WHERE row_num = 1  AND (jz_create_on::timestamp <= '" + endTime + " 23:59:59' OR cj_create_on::timestamp <= '" + endTime + " 23:59:59') " +
                  "AND (jz_fwzt IS NOT NULL OR cj_fwzt IS NOT NULL))  order by village)  t  where 1=1  " + where + " limit " + pageSize + " offset(" + (pageIndex - 1) + ") * " + pageSize;
                    sql_count = @" select count(1) cnt from (SELECT t3.* FROM ( SELECT DISTINCT ON (t1.fwbh) t1.ID,t1.xzq,t1.xzqdm,t1.villagedm,t1.towndm,t1.town, t1.address,t1.hzxm,t1.village," +
                       "   t1.cs, t1.jzscxyaqcksl,t1.jzxyssltsl,t1.sstdjsqk,t1.xggdjsbp, t1.fwbh,jsrysstd,t1.yhly,t1.create_on,t1.create_name  FROM zm_pc_baselog_cj t1  WHERE create_on BETWEEN '" + startTime + "' AND  '" + endTime + " 23:59:59'  " + xzqWhere +
                      "     ORDER BY t1.fwbh, t1.create_on DESC ) t3" +
                     "  INNER JOIN zm_pc_yhlog_cj m2 ON t3.ID = m2.base_id" +
                       " WHERE t3.create_on BETWEEN '" + startTime + "' AND  '" + endTime + " 23:59:59' " + strWhere + " and jsrysstd='" + jsrysstd + "'  " +
                        " AND t3.fwbh not in ( select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) 	and (final_create_on = '" + endTime + "' ) )  order by village)  t  where 1=1  " + where;

                }
            }
            else if(roletype == "2" || roletype == "3" || roletype == "4")
            {

                if (jsrysstd == "ljbhg")
                {
                    sql = "SELECT ROW_NUMBER( ) OVER ( ) index,* FROM\t(\t\tWITH building_first_second AS (    select t1.* from (SELECT        " +
                        "t1.ID,t1.xzq,t1.xzqdm,t1.villagedm,t1.towndm,t1.town, t1.address,t1.hzxm,t1.village, t1.cs, t1.jzscxyaqcksl,t1.jzxyssltsl,t1.xggdjsbp, " +
                        "t1.fwbh,jsrysstd,t1.create_on,t1.create_name,t1.yhly,  FIRST_VALUE(jsrysstd) OVER (PARTITION BY fwbh ORDER BY create_on desc) AS first_jsrysstd,     " +
                        "   LEAD(jsrysstd, 1) OVER (PARTITION BY fwbh ORDER BY create_on desc) AS second_jsrysstd    FROM        " +
                        " zm_pc_baselog_sqjz t1 where create_on BETWEEN '"+ startTime + "' AND '"+ endTime + " 23:59:59' and yhly='"+roletype+"') t1    INNER JOIN     " +
                        "   zm_pc_yhlog_sqjz t2 ON t1.id = t2.base_id    WHERE  t1.create_on BETWEEN '"+ startTime + "' AND '"+ endTime + " 23:59:59' and yhly='"+roletype+"'    " +
                        "    AND t1.fwbh NOT IN ( select DISTINCT fwbh from "+ table_fwzt + " where  (final_fwzt IS NOT NULL) 	and (final_create_on = '"+endTime+ " 23:59:59' )  ))\t select * from (select ROW_NUMBER() OVER (PARTITION BY fwbh ORDER BY create_on DESC) AS row_num," +
                        " *FROM     building_first_second where ((first_jsrysstd='否') OR ( second_jsrysstd = '否' AND first_jsrysstd = '是'))) t" +
                        " where 1=1  "+ where + " and row_num=1 order by create_on desc) T   WHERE  1 = 1   " +
                         where + "  limit " + pageSize + " offset(" + (pageIndex - 1) + ") * " + pageSize;
                    sql_count = "SELECT count(1) cnt FROM\t(\t\tWITH building_first_second AS (    select t1.* from (SELECT        " +
                        "t1.ID,t1.xzq,t1.xzqdm,t1.villagedm,t1.towndm,t1.town, t1.address,t1.hzxm,t1.village, t1.cs, t1.jzscxyaqcksl,t1.jzxyssltsl,t1.xggdjsbp, " +
                        "t1.fwbh,jsrysstd,t1.create_on,t1.create_name,t1.yhly,  FIRST_VALUE(jsrysstd) OVER (PARTITION BY fwbh ORDER BY create_on desc) AS first_jsrysstd,     " +
                        "   LEAD(jsrysstd, 1) OVER (PARTITION BY fwbh ORDER BY create_on desc) AS second_jsrysstd    FROM        " +
                        " zm_pc_baselog_sqjz t1 where create_on BETWEEN '" + startTime + "' AND '" + endTime + " 23:59:59' and yhly='"+ roletype + "') t1    INNER JOIN     " +
                        "   zm_pc_yhlog_sqjz t2 ON t1.id = t2.base_id    WHERE  t1.create_on BETWEEN '" + startTime + "' AND '" + endTime + " 23:59:59' and yhly='"+roletype+"'    " +
                        "    AND t1.fwbh NOT IN (select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) 	and (final_create_on = '" + endTime + " 23:59:59' )      ))\t select * from (select ROW_NUMBER() OVER (PARTITION BY fwbh ORDER BY create_on DESC) AS row_num," +
                        " *FROM     building_first_second where ((first_jsrysstd='否') OR ( second_jsrysstd = '否' AND first_jsrysstd = '是'))) t" +
                        " where 1=1  " + where + " and row_num=1 order by create_on desc) T   WHERE  1 = 1   " +
                         where;
                }
                else if (jsrysstd == "zg")
                {
                    sql = "SELECT ROW_NUMBER( ) OVER ( ) index,* FROM\t(\t\tWITH building_first_second AS (    select t1.* from (SELECT        " +
                       "t1.ID,t1.xzq,t1.xzqdm,t1.villagedm,t1.towndm,t1.town, t1.address,t1.hzxm,t1.village, t1.cs, t1.jzscxyaqcksl,t1.jzxyssltsl,t1.xggdjsbp, " +
                       "t1.fwbh,jsrysstd,t1.create_on,t1.create_name,t1.yhly,  FIRST_VALUE(jsrysstd) OVER (PARTITION BY fwbh ORDER BY create_on desc) AS first_jsrysstd,     " +
                       "   LEAD(jsrysstd, 1) OVER (PARTITION BY fwbh ORDER BY create_on desc) AS second_jsrysstd    FROM        " +
                       " zm_pc_baselog_sqjz t1 where create_on BETWEEN '" + startTime + "' AND '" + endTime + " 23:59:59' and yhly='"+ roletype + "') t1    INNER JOIN     " +
                       "   zm_pc_yhlog_sqjz t2 ON t1.id = t2.base_id    WHERE  t1.create_on BETWEEN '" + startTime + "' AND '" + endTime + " 23:59:59' and yhly='"+ roletype + "'    " +
                       "    AND t1.fwbh NOT IN (select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) 	and (final_create_on = '" + endTime + " 23:59:59' )      ))\t select * from (select ROW_NUMBER() OVER (PARTITION BY fwbh ORDER BY create_on DESC) AS row_num," +
                       " *FROM     building_first_second where (( second_jsrysstd = '否' AND first_jsrysstd = '是'))) t" +
                       " where 1=1  " + where + " and row_num=1 order by create_on desc) T   WHERE  1 = 1   " +
                        where + "  limit " + pageSize + " offset(" + (pageIndex - 1) + ") * " + pageSize;
                    sql_count = "SELECT count(1) cnt FROM\t(\t\tWITH building_first_second AS (    select t1.* from (SELECT        " +
                         "t1.ID,t1.xzq,t1.xzqdm,t1.villagedm,t1.towndm,t1.town, t1.address,t1.hzxm,t1.village, t1.cs, t1.jzscxyaqcksl,t1.jzxyssltsl,t1.xggdjsbp, " +
                         "t1.fwbh,jsrysstd,t1.create_on,t1.create_name,t1.yhly,  FIRST_VALUE(jsrysstd) OVER (PARTITION BY fwbh ORDER BY create_on desc) AS first_jsrysstd,     " +
                         "   LEAD(jsrysstd, 1) OVER (PARTITION BY fwbh ORDER BY create_on desc) AS second_jsrysstd    FROM        " +
                         " zm_pc_baselog_sqjz t1 where create_on BETWEEN '" + startTime + "' AND '" + endTime + " 23:59:59' and yhly='"+ roletype + "') t1    INNER JOIN     " +
                         "   zm_pc_yhlog_sqjz t2 ON t1.id = t2.base_id    WHERE  t1.create_on BETWEEN '" + startTime + "' AND '" + endTime + " 23:59:59' and yhly='"+ roletype + "'    " +
                         "    AND t1.fwbh NOT IN (select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) 	and (final_create_on = '" + endTime + " 23:59:59' )     ))\t select * from (select ROW_NUMBER() OVER (PARTITION BY fwbh ORDER BY create_on DESC) AS row_num," +
                         " *FROM     building_first_second where (( second_jsrysstd = '否' AND first_jsrysstd = '是'))) t" +
                         " where 1=1  " + where + " and row_num=1 order by create_on desc) T   WHERE  1 = 1   " +
                          where;
                }
                else
                {
                    sql = @" select ROW_NUMBER() OVER ()index,* from (SELECT t3.* FROM ( SELECT DISTINCT ON (t1.fwbh) t1.ID,t1.xzq,t1.xzqdm,t1.villagedm,t1.towndm,t1.town, t1.address,t1.hzxm,t1.village," +
                    "   t1.cs, t1.jzscxyaqcksl,t1.jzxyssltsl,t1.sstdjsqk,t1.xggdjsbp, t1.fwbh,jsrysstd,t1.yhly,t1.create_on,t1.create_name  FROM zm_pc_baselog_sqjz t1  WHERE create_on BETWEEN '" + startTime + "' AND  '" + endTime + " 23:59:59'  and yhly='" + roletype + "' " + xzqWhere +
                   "     ORDER BY t1.fwbh, t1.create_on DESC ) t3" +
                  "  INNER JOIN zm_pc_yhlog_sqjz m2 ON t3.ID = m2.base_id" +
                    " WHERE t3.create_on BETWEEN '" + startTime + "' AND  '" + endTime + " 23:59:59' " + strWhere + " and jsrysstd='" + jsrysstd + "' and t3.yhly='" + roletype + "' " +
                     " AND t3.fwbh not in ( select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) 	and (final_create_on = '" + endTime + " 23:59:59' ) )  order by village)  t  where 1=1  " + where + " limit " + pageSize + " offset(" + (pageIndex - 1) + ") * " + pageSize;
                    sql_count = @" select count(1) cnt from (SELECT t3.* FROM ( SELECT DISTINCT ON (t1.fwbh) t1.ID,t1.xzq,t1.xzqdm,t1.villagedm,t1.towndm,t1.town, t1.address,t1.hzxm,t1.village," +
                            "   t1.cs, t1.jzscxyaqcksl,t1.jzxyssltsl,t1.sstdjsqk,t1.xggdjsbp, t1.fwbh,jsrysstd,t1.yhly,t1.create_on,t1.create_name  FROM zm_pc_baselog_sqjz t1  WHERE create_on BETWEEN '" + startTime + "' AND  '" + endTime + " 23:59:59'  and yhly='" + roletype + "' " + xzqWhere +
                           "     ORDER BY t1.fwbh, t1.create_on DESC ) t3" +
                          "  INNER JOIN zm_pc_yhlog_sqjz m2 ON t3.ID = m2.base_id" +
                            " WHERE t3.create_on BETWEEN '" + startTime + "' AND  '" + endTime + " 23:59:59' " + strWhere + " and jsrysstd='" + jsrysstd + "' and t3.yhly='" + roletype + "' " +
                             " AND t3.fwbh not in (select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) 	and (final_create_on = '" + endTime + " 23:59:59' ) )  order by village)  t  where 1=1   " + where;

                }
            }
            if (cked == "true") 
            {
                if (roletype == "2" || roletype == "3" || roletype == "4")
                {

                    if (jsrysstd == "ljbhg")
                    {
                        sql = "SELECT ROW_NUMBER( ) OVER ( ) index,* FROM\t(\t\tWITH building_first_second AS (    select t1.* from (SELECT        " +
                            "t1.ID,t1.xzq,t1.xzqdm,t1.villagedm,t1.towndm,t1.town, t1.address,t1.hzxm,t1.village, t1.cs, t1.jzscxyaqcksl,t1.jzxyssltsl,t1.xggdjsbp, " +
                            "t1.fwbh,jsrysstd,t1.create_on,t1.create_name,t1.yhly,  FIRST_VALUE(jsrysstd) OVER (PARTITION BY fwbh ORDER BY create_on desc) AS first_jsrysstd,     " +
                            "   LEAD(jsrysstd, 1) OVER (PARTITION BY fwbh ORDER BY create_on desc) AS second_jsrysstd    FROM        " +
                            " zm_pc_baselog_sqjz t1 where create_on BETWEEN '" + startTime + "' AND '" + endTime + " 23:59:59' and yhly='"+ roletype + "') t1    INNER JOIN     " +
                            "   zm_pc_yhlog_sqjz t2 ON t1.id = t2.base_id    WHERE  t1.create_on BETWEEN '" + startTime + "' AND '" + endTime + " 23:59:59' and yhly='"+ roletype + "'    " +
                            "    AND t1.fwbh NOT IN ( select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) 	and (final_create_on = '" + endTime + " 23:59:59' )  ))\t select * from (select ROW_NUMBER() OVER (PARTITION BY fwbh ORDER BY create_on DESC) AS row_num," +
                            " *FROM     building_first_second where ((first_jsrysstd='否') OR ( second_jsrysstd = '否' AND first_jsrysstd = '是'))) t" +
                            " where 1=1  " + where + " and row_num=1 order by create_on desc) T   WHERE  1 = 1  and village in(select villagedm from zdcmd) " +
                             where + "  limit " + pageSize + " offset(" + (pageIndex - 1) + ") * " + pageSize;
                        sql_count = "SELECT count(1) cnt FROM\t(\t\tWITH building_first_second AS (    select t1.* from (SELECT        " +
                            "t1.ID,t1.xzq,t1.xzqdm,t1.villagedm,t1.towndm,t1.town, t1.address,t1.hzxm,t1.village, t1.cs, t1.jzscxyaqcksl,t1.jzxyssltsl,t1.xggdjsbp, " +
                            "t1.fwbh,jsrysstd,t1.create_on,t1.create_name,t1.yhly,  FIRST_VALUE(jsrysstd) OVER (PARTITION BY fwbh ORDER BY create_on desc) AS first_jsrysstd,     " +
                            "   LEAD(jsrysstd, 1) OVER (PARTITION BY fwbh ORDER BY create_on desc) AS second_jsrysstd    FROM        " +
                            " zm_pc_baselog_sqjz t1 where create_on BETWEEN '" + startTime + "' AND '" + endTime + " 23:59:59' and yhly='"+ roletype + "') t1    INNER JOIN     " +
                            "   zm_pc_yhlog_sqjz t2 ON t1.id = t2.base_id    WHERE  t1.create_on BETWEEN '" + startTime + "' AND '" + endTime + " 23:59:59' and yhly='"+ roletype + "'    " +
                            "    AND t1.fwbh NOT IN (select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) 	and (final_create_on = '" + endTime + " 23:59:59' )      ))\t select * from (select ROW_NUMBER() OVER (PARTITION BY fwbh ORDER BY create_on DESC) AS row_num," +
                            " *FROM     building_first_second where ((first_jsrysstd='否') OR ( second_jsrysstd = '否' AND first_jsrysstd = '是'))) t" +
                            " where 1=1  " + where + " and row_num=1 order by create_on desc) T   WHERE  1 = 1 and village in(select villagedm from zdcmd)  " +
                             where;
                    }
                    else if (jsrysstd == "zg")
                    {
                        sql = "SELECT ROW_NUMBER( ) OVER ( ) index,* FROM\t(\t\tWITH building_first_second AS (    select t1.* from (SELECT        " +
                           "t1.ID,t1.xzq,t1.xzqdm,t1.villagedm,t1.towndm,t1.town, t1.address,t1.hzxm,t1.village, t1.cs, t1.jzscxyaqcksl,t1.jzxyssltsl,t1.xggdjsbp, " +
                           "t1.fwbh,jsrysstd,t1.create_on,t1.create_name,t1.yhly,  FIRST_VALUE(jsrysstd) OVER (PARTITION BY fwbh ORDER BY create_on desc) AS first_jsrysstd,     " +
                           "   LEAD(jsrysstd, 1) OVER (PARTITION BY fwbh ORDER BY create_on desc) AS second_jsrysstd    FROM        " +
                           " zm_pc_baselog_sqjz t1 where create_on BETWEEN '" + startTime + "' AND '" + endTime + " 23:59:59' and yhly='"+ roletype + "') t1    INNER JOIN     " +
                           "   zm_pc_yhlog_sqjz t2 ON t1.id = t2.base_id    WHERE  t1.create_on BETWEEN '" + startTime + "' AND '" + endTime + " 23:59:59' and yhly='"+ roletype + "'    " +
                           "    AND t1.fwbh NOT IN (select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) 	and (final_create_on = '" + endTime + " 23:59:59' )      ))\t select * from (select ROW_NUMBER() OVER (PARTITION BY fwbh ORDER BY create_on DESC) AS row_num," +
                           " *FROM     building_first_second where (( second_jsrysstd = '否' AND first_jsrysstd = '是'))) t" +
                           " where 1=1  " + where + " and row_num=1 order by create_on desc) T   WHERE  1 = 1 and village in(select villagedm from zdcmd)  " +
                            where + "  limit " + pageSize + " offset(" + (pageIndex - 1) + ") * " + pageSize;
                        sql_count = "SELECT count(1) cnt FROM\t(\t\tWITH building_first_second AS (    select t1.* from (SELECT        " +
                             "t1.ID,t1.xzq,t1.xzqdm,t1.villagedm,t1.towndm,t1.town, t1.address,t1.hzxm,t1.village, t1.cs, t1.jzscxyaqcksl,t1.jzxyssltsl,t1.xggdjsbp, " +
                             "t1.fwbh,jsrysstd,t1.create_on,t1.create_name,t1.yhly,  FIRST_VALUE(jsrysstd) OVER (PARTITION BY fwbh ORDER BY create_on desc) AS first_jsrysstd,     " +
                             "   LEAD(jsrysstd, 1) OVER (PARTITION BY fwbh ORDER BY create_on desc) AS second_jsrysstd    FROM        " +
                             " zm_pc_baselog_sqjz t1 where create_on BETWEEN '" + startTime + "' AND '" + endTime + " 23:59:59' and yhly='"+ roletype + "') t1    INNER JOIN     " +
                             "   zm_pc_yhlog_sqjz t2 ON t1.id = t2.base_id    WHERE  t1.create_on BETWEEN '" + startTime + "' AND '" + endTime + " 23:59:59' and yhly='"+ roletype + "'    " +
                             "    AND t1.fwbh NOT IN (select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) 	and (final_create_on = '" + endTime + " 23:59:59' )     ))\t select * from (select ROW_NUMBER() OVER (PARTITION BY fwbh ORDER BY create_on DESC) AS row_num," +
                             " *FROM     building_first_second where (( second_jsrysstd = '否' AND first_jsrysstd = '是'))) t" +
                             " where 1=1  " + where + " and row_num=1 order by create_on desc) T   WHERE  1 = 1 and village in(select villagedm from zdcmd)  " +
                              where;
                    }
                    else
                    {
                        sql = @" select ROW_NUMBER() OVER ()index,* from (SELECT t3.* FROM ( SELECT DISTINCT ON (t1.fwbh) t1.ID,t1.xzq,t1.xzqdm,t1.villagedm,t1.towndm,t1.town, t1.address,t1.hzxm,t1.village," +
                        "   t1.cs, t1.jzscxyaqcksl,t1.jzxyssltsl,t1.sstdjsqk,t1.xggdjsbp, t1.fwbh,jsrysstd,t1.yhly,t1.create_on,t1.create_name  FROM zm_pc_baselog_sqjz t1  WHERE create_on BETWEEN '" + startTime + "' AND  '" + endTime + " 23:59:59'  and yhly='" + roletype + "' " + xzqWhere +
                       "     ORDER BY t1.fwbh, t1.create_on DESC ) t3" +
                      "  INNER JOIN zm_pc_yhlog_sqjz m2 ON t3.ID = m2.base_id" +
                        " WHERE t3.create_on BETWEEN '" + startTime + "' AND  '" + endTime + " 23:59:59' " + strWhere + " and jsrysstd='" + jsrysstd + "' and t3.yhly='" + roletype + "' " +
                         " AND t3.fwbh not in ( select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) 	and (final_create_on = '" + endTime + " 23:59:59' ) )  order by village)  t  where 1=1 and village in(select villagedm from zdcmd)  " + where + " limit " + pageSize + " offset(" + (pageIndex - 1) + ") * " + pageSize;
                        sql_count = @" select count(1) cnt from (SELECT t3.* FROM ( SELECT DISTINCT ON (t1.fwbh) t1.ID,t1.xzq,t1.xzqdm,t1.villagedm,t1.towndm,t1.town, t1.address,t1.hzxm,t1.village," +
                                "   t1.cs, t1.jzscxyaqcksl,t1.jzxyssltsl,t1.sstdjsqk,t1.xggdjsbp, t1.fwbh,jsrysstd,t1.yhly,t1.create_on,t1.create_name  FROM zm_pc_baselog_sqjz t1  WHERE create_on BETWEEN '" + startTime + "' AND  '" + endTime + " 23:59:59'  and yhly='" + roletype + "' " + xzqWhere +
                               "     ORDER BY t1.fwbh, t1.create_on DESC ) t3" +
                              "  INNER JOIN zm_pc_yhlog_sqjz m2 ON t3.ID = m2.base_id" +
                                " WHERE t3.create_on BETWEEN '" + startTime + "' AND  '" + endTime + " 23:59:59' " + strWhere + " and jsrysstd='" + jsrysstd + "' and t3.yhly='" + roletype + "' " +
                                 " AND t3.fwbh not in (select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) 	and (final_create_on = '" + endTime + " 23:59:59' ) )  order by village)  t  where 1=1 and village in(select villagedm from zdcmd)   " + where;

                    }
                }
            }

            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
        || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
        || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            //执行语句
            var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);

            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = list, NextPageIndex = pageIndex, PageTotal = count, Total = count });

        }
        /// <summary>
        /// 获取所有的核验人员姓名、单位
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("targeted/std/getUserByList")]
        public RespRet<Page<object>> getUserByList([FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] string roletype, [FromQuery] int pageIndex, [FromQuery] int pageSize, [FromQuery] string startTime, [FromQuery] string endTime, [FromQuery] string txtKeyWord)
        {
            if (pageIndex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }

            var strWhere = "";
            string district = user.District;

            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);
            if (string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime))
            {
                startTime = DateTime.Now.ToShortDateString();
                endTime = DateTime.Now.ToShortDateString();
            }

            if (user.RoleType == 1 || user.RoleType == 2)
            {
                strWhere += " and xzq!='东城区' and xzq!='西城区' ";
            }
            else if (user.RoleType == 3)
            {
                strWhere += " and xzq='" + model.RegionName + "'";
            }
            else
            {

                strWhere += " and xzqdm='" + model.RegionCode.Substring(0, 6) + "'";
            }
            if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && !string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and \"number\" in(select distinct create_by from zm_pc_baselog_sqjz where 1=1 and xzqdm='" + xzqdm + "' and town='" + towndm + "' and village='" + villagedm + "' and yhly='"+roletype+ "' and create_on BETWEEN '"+startTime+"' and '"+endTime+"' )  ";
            }
            else if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and \"number\" in(select distinct create_by from zm_pc_base_sqjz where 1=1 and xzqdm='" + xzqdm + "' and town='" + towndm + "' and yhly='" + roletype + "' and create_on BETWEEN '"+startTime+"' and '"+endTime+"')  ";
            }
            else if (!string.IsNullOrEmpty(xzqdm) && string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and \"number\" in(select distinct create_by from zm_pc_base_sqjz where  1=1 and xzqdm='" + xzqdm + "' and yhly='" + roletype + "' and create_on BETWEEN '"+startTime+"' and '"+endTime+"')  ";
            }

            if (!string.IsNullOrEmpty(txtKeyWord))
            {
                strWhere += " and (nickname like '%" + txtKeyWord + "%')";
            }
            if (!string.IsNullOrEmpty(roletype))
            {
                strWhere += " and (role_type='" + roletype + "')";
            }
            string today = DateTime.Now.ToString("yyyy-MM-dd");
            string table_fwzt = "";
            if (!string.IsNullOrEmpty(endTime))
            {
                DateTime endData = Convert.ToDateTime(endTime);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            else
            {
                DateTime endData = Convert.ToDateTime(today);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            string sql = "select ROW_NUMBER() OVER ()index,* from (select * from (select t1.*,coalesce(t2.hccount,0)hccount,t3.region_name xzq,t4.region_name town,t5.region_name village,CASE " +
                                    "   WHEN COALESCE(t3.region_name, '') = '' AND COALESCE(t4.region_name, '') = '' THEN '' " +
                                      " WHEN COALESCE(t4.region_name, '') = '' THEN COALESCE(t3.region_name, '') " +
                                       " ELSE CONCAT(t3.region_name, '-', t4.region_name) " +
                                  " END AS town_village from(select number, nickname, role_type, (select id xzqdm from zm_department where id = (SUBSTRING(district->> 'RegionCode', 0, 7))),  " +
                                     "(case when role_type= '4' then(select id towndm from zm_department where id = concat(SUBSTRING(district->> 'RegionCode', 0, 10), '000'))  else case when role_type= '5' then(select id villagedm  " +
                                     " from zm_department where id = district->> 'RegionCode')  else '' end end) towndm,  (case when role_type= '5' then(select id villagedm from zm_department where id = district->> 'RegionCode')   " +
                                  "     else '' end) villagedm,       case when role_type= '5' then   '村级用户'  else case when role_type= '4' then '乡镇用户' else case when role_type= '3'  then '区级用户' else '市级用户' " +
                                    "    end end end role_type_name, to_char(t1.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS') create_on,t1.create_ip,qaakey,t1.unitname from zm_user_cj t1)t1 " +
                                     "   left join(select create_by, count(DISTINCT fwbh) hccount, jcly from zm_pc_yhlog_sqjz where date(create_on) BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59' and jcly='"+ roletype + "' and fwbh not in " +
                                    "     (select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) 	and (final_create_on = '" + endTime + " 23:59:59' ) ) " +
                                     "    group by create_by, jcly) t2 on t1.number = t2.create_by left join zm_department t3 on t1.xzqdm = t3.id     left join zm_department t4 on t1.towndm = t4.id  " +
                       "  left join zm_department t5 on t1.villagedm = t5.id  order by t1.create_on desc) t where 1 = 1 and  hccount>0  " + strWhere + " order by nickname) t  limit " + pageSize + " offset (" + pageIndex + " - 1) * " + pageSize + ""; ;
            if (sql.Contains("insert    into", StringComparison.OrdinalIgnoreCase)
            || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
||             sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            // string sql_count = "select count(1) cnt from (select t.*,region_name xzq from (select xzqdm,SUM(CASE WHEN role_type='村级用户' THEN usercount ELSE 0 END) AS villageusercount,    SUM(CASE WHEN role_type='乡镇用户' THEN usercount ELSE 0 END) AS townusercount,    SUM(CASE WHEN role_type='区级用户' THEN usercount ELSE 0 END) AS xzqusercount,    SUM(CASE WHEN role_type='市级用户' THEN usercount ELSE 0 END) AS sjusercount,\t\tSUM(usercount) AS usercount\t\t from (select  xzqdm,role_type,count(1) usercountfrom (select number, nickname,(select  id xzqdm from zm_department where id =(SUBSTRING(district ->> 'RegionCode' ,0,7))),case when role_type='4' then '' else (select id towndm from zm_department where id =concat(SUBSTRING(district ->> 'RegionCode' ,0,10),'000')) end , case when role_type='3' then '' else  (select id villagedm from zm_department where id =district ->> 'RegionCode') end,case when role_type='5' then   '村级用户'  else case when role_type='4' then '乡镇用户' else case when role_type='3'  then '区级用户' else  '市级用户' end end end role_type,to_char(t1.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS') create_on,t1.create_ip,qaakey from zm_user_cj t1 ) t1   group by xzqdm,role_type) t   group by xzqdm) t left join zm_department m on t.xzqdm=m.id) t where 1 = 1   " + strWhere + " ";
            string sql_count = "select count(1) cnt from (select t1.*,coalesce(t2.hccount,0)hccount,t3.region_name xzq,t4.region_name town,t5.region_name village,CASE " +
                         "   WHEN COALESCE(t3.region_name, '') = '' AND COALESCE(t4.region_name, '') = '' THEN '' " +
                           " WHEN COALESCE(t4.region_name, '') = '' THEN COALESCE(t3.region_name, '') " +
                            " ELSE CONCAT(t3.region_name, '-', t4.region_name) " +
                       " END AS town_village from(select number, nickname, role_type, (select id xzqdm from zm_department where id = (SUBSTRING(district->> 'RegionCode', 0, 7))),  " +
                          "(case when role_type= '4' then(select id towndm from zm_department where id = concat(SUBSTRING(district->> 'RegionCode', 0, 10), '000'))  else case when role_type= '5' then(select id villagedm  " +
                          " from zm_department where id = district->> 'RegionCode')  else '' end end) towndm,  (case when role_type= '5' then(select id villagedm from zm_department where id = district->> 'RegionCode')   " +
                       "     else '' end) villagedm,       case when role_type= '5' then   '村级用户'  else case when role_type= '4' then '乡镇用户' else case when role_type= '3'  then '区级用户' else '市级用户' " +
                         "    end end end role_type_name, to_char(t1.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS') create_on,t1.create_ip,qaakey,t1.unitname from zm_user_cj t1)t1 " +
                          "   left join(select create_by, count(DISTINCT fwbh) hccount, jcly from zm_pc_yhlog_sqjz where date(create_on) BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59'  and jcly='"+ roletype + "' and fwbh not in " +
                         "     (select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) 	and (final_create_on = '" + endTime + " 23:59:59' ) ) " +
                          "    group by create_by, jcly) t2 on t1.number = t2.create_by left join zm_department t3 on t1.xzqdm = t3.id     left join zm_department t4 on t1.towndm = t4.id  " +
                          "  left join zm_department t5 on t1.villagedm = t5.id  order by t1.create_on desc ) t where 1 = 1 and  hccount>0   " + strWhere;


            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
          || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
          || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            //执行语句
            var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);

            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = list, NextPageIndex = pageIndex, PageTotal = count, Total = count });

        }
        /// <summary>
        /// 导出双通道统计情况
        /// </summary>
        /// <param name="pageRequest">type check</param>
        /// <returns></returns>
        [HttpGet("targeted/sysstdExportExcel")]
        [Produces("application/octet-stream")]
        //[ProducesResponseType(200)]
        //[ProducesResponseType(401)]
        public IActionResult sysstdExportExcel([FromQuery] string startTime, [FromQuery] string endTime, [FromQuery] string roletype, [FromQuery] string xzqdm, [FromQuery] string towndm)
        {
            try
            {
                //var list = new List<VillageFireSafetyAttrDto>();
                var userNumber = this.CurrentUser();

                var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
                var sbSql = new StringBuilder();
                var sbSqlWhere = new StringBuilder();
                var sqlParams = new List<NpgsqlParameter>();

                var sbSqlBuildingEx = new StringBuilder();

                string district = user.District;

                var options = new JsonSerializerOptions();
                options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
                Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


                string strWhere = "  ";

                if (user.RoleType == 1 || user.RoleType == 2)
                {
                    strWhere += " and xzq!='东城区' and xzq!='西城区' ";
                }
                else if (user.RoleType == 3)  //区级
                {
                    strWhere += " and xzqdm='" + model.RegionCode + "'";
                }
                else if (user.RoleType == 4)
                {
                    string tdm = model.RegionCode;
                    if (model.RegionCode.Length != 12)
                    {
                        tdm = model.RegionCode + "000";
                    }
                    strWhere += " and towndm='" + tdm + "'";
                }
                else if (user.RoleType == 5)
                {
                    strWhere += " and villagedm='" + model.RegionCode + "'";
                }
                if (string.IsNullOrEmpty(startTime))
                {
                    startTime = "2024-01-01";
                    endTime = DateTime.Now.ToString("yyyy-MM-dd");
                }
                if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm))
                {
                    strWhere += " and xzqdm='" + xzqdm + "' and towndm='" + towndm + "'";
                }else if (!string.IsNullOrEmpty(xzqdm) && string.IsNullOrEmpty(towndm))
                {
                    strWhere += " and xzqdm='" + xzqdm + "'";
                }
                string table_baselog = "";
                string table_yhlog = "";
                string yhly = "";
                if (roletype == "2" || roletype == "3" || roletype == "4")
                {
                    table_baselog = "zm_pc_baselog_sqjz";
                    table_yhlog = "zm_pc_yhlog_sqjz";
                    yhly = roletype;
                }
                else if (roletype == "5")
                {
                    table_baselog = "zm_pc_baselog_cj";
                    table_yhlog = "zm_pc_yhlog_cj";
                }
                string today = DateTime.Now.ToString("yyyy-MM-dd");
                string table_fwzt = "";
                if (!string.IsNullOrEmpty(endTime))
                {
                    DateTime endData = Convert.ToDateTime(endTime);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                }
                else
                {
                    DateTime endData = Convert.ToDateTime(today);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                }
                string sql = "";
                if (string.IsNullOrEmpty(xzqdm))
                {
                    sql = "  select ROW_NUMBER() OVER()index ,* from(SELECT * FROM pro_std_gq('" + startTime + "', '" + endTime + " 23:59:59', '" + yhly + "','" + table_baselog + "','" + table_yhlog + "','"+ table_fwzt + "') order by xzqdm) t where 1=1" + strWhere;
                }
                else if (!string.IsNullOrEmpty(xzqdm) && string.IsNullOrEmpty(towndm))
                {
                    sql = "  select ROW_NUMBER() OVER()index ,* from(SELECT * FROM pro_std_xz('" + startTime + "', '" + endTime + " 23:59:59', '" + yhly + "','" + table_baselog + "','" + table_yhlog + "','"+ table_fwzt + "') order by xzqdm) t where 1=1" + strWhere;
                }
                else if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm))
                {
                    sql = "  select ROW_NUMBER() OVER()index ,* from(SELECT * FROM pro_std_cj('" + startTime + "', '" + endTime + " 23:59:59', '" + yhly + "','" + table_baselog + "','" + table_yhlog + "','"+ table_fwzt + "') order by xzqdm) t where 1=1" + strWhere;
                }

                if (roletype == "6")
                {
                    if (string.IsNullOrEmpty(xzqdm))
                    {
                        sql = "  select ROW_NUMBER() OVER()index ,* from(SELECT * FROM pro_std_gq_zc( '" + endTime + " 23:59:59','" + table_fwzt + "') order by xzqdm) t where 1=1" + strWhere;
                    }
                    else if (!string.IsNullOrEmpty(xzqdm) && string.IsNullOrEmpty(towndm))
                    {
                        sql = "  select ROW_NUMBER() OVER()index ,* from(SELECT * FROM pro_std_xz_zc( '" + endTime + " 23:59:59','" + table_fwzt + "') order by xzqdm) t where 1=1" + strWhere;
                    }
                    else if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm))
                    {
                        sql = "  select ROW_NUMBER() OVER()index ,* from(SELECT * FROM pro_std_cj_zc( '" + endTime + " 23:59:59','" + table_fwzt + "') order by xzqdm) t where 1=1" + strWhere;
                    }
                }
           
                //写入缓存
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet(model.RegionName + "");
                // 第一行 标题
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("序号");
                row.CreateCell(1).SetCellValue("房屋总量");
                row.CreateCell(2).SetCellValue("已核验房屋");
                row.CreateCell(3).SetCellValue("核验进度");
                row.CreateCell(4).SetCellValue("合格房屋");
                row.CreateCell(5).SetCellValue("当前合格率");
                row.CreateCell(6).SetCellValue("发现不合格房屋");
                row.CreateCell(7).SetCellValue("已整改");
                row.CreateCell(8).SetCellValue("未整改");
                row.CreateCell(9).SetCellValue("整改率");
                row.CreateCell(10).SetCellValue("当前不合格率");
                row.CreateCell(11).SetCellValue("一层合格率");
                row.CreateCell(12).SetCellValue("二层合格率");
                row.CreateCell(13).SetCellValue("三层及以上合格率");
                if (string.IsNullOrEmpty(xzqdm))
                {
                    row.CreateCell(14).SetCellValue("区名称");
                }
                else if (!string.IsNullOrEmpty(xzqdm) && string.IsNullOrEmpty(towndm))
                {
                    row.CreateCell(14).SetCellValue("区名称");
                    row.CreateCell(15).SetCellValue("乡镇");
                }
                else if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm))
                {
                    row.CreateCell(14).SetCellValue("区名称");
                    row.CreateCell(15).SetCellValue("乡镇");
                    row.CreateCell(16).SetCellValue("村名称");
                }
                // 第二行 写数据
                int i = 1;
                foreach (object obj in data)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    if (obj != null)
                    {

                        var properties = obj as IDictionary<string, object>;
                        row = sheet.CreateRow(i);
                        foreach (var kvp in properties)
                        {
                            var val = kvp.Value+"";
                            if (kvp.Key == "index")
                            {
                                row.CreateCell(0).SetCellValue(i);
                            }
                            else if (kvp.Key == "zcount")
                            {
                                row.CreateCell(1).SetCellValue(val);
                            }
                            else if (kvp.Key == "zccount")
                            {
                                row.CreateCell(2).SetCellValue(val);
                            }
                            else if (kvp.Key == "hy_pencent")
                            {
                                row.CreateCell(3).SetCellValue(val);
                            }
                            else if (kvp.Key == "ssltdbfjsl")
                            {
                                row.CreateCell(4).SetCellValue(val);
                            }
                            else if (kvp.Key == "dbl_pencent")
                            {
                                row.CreateCell(5).SetCellValue(val);
                            }
                            else if (kvp.Key == "first_no_count")
                            {
                                row.CreateCell(6).SetCellValue(val);
                            }
                            else if (kvp.Key == "second_yes_count")
                            {
                                row.CreateCell(7).SetCellValue(val);
                            }
                            else if (kvp.Key == "wwccount")
                            {
                                row.CreateCell(8).SetCellValue(val);
                            }
                            else if (kvp.Key == "zgl_pencent")
                            {
                                row.CreateCell(9).SetCellValue(val);
                            }
                            else if (kvp.Key == "wdbl_percent")
                            {
                                row.CreateCell(10).SetCellValue(val);
                            }
                            else if (kvp.Key == "one_pencent")
                            {
                                row.CreateCell(11).SetCellValue(val);
                            }
                            else if (kvp.Key == "two_pencent")
                            {
                                row.CreateCell(12).SetCellValue(val);
                            }
                            else if (kvp.Key == "three_pencent") 
                            {
                                row.CreateCell(13).SetCellValue(val);
                            }
                            else if (kvp.Key == "xzq")
                            {
                                row.CreateCell(14).SetCellValue(val);
                            }
                            else if (kvp.Key == "town")
                            {
                                row.CreateCell(15).SetCellValue(val);
                            }
                            else if (kvp.Key == "village")
                            {
                                row.CreateCell(16).SetCellValue(val);
                            }

                        }

                        i++;
                    }
                }

                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "人员疏散通道建设情况" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx");
            }
            catch (Exception ex)
            {
                throw;
            }

        }
        /// <summary>
        /// 导出双通道统计情况(重点村)
        /// </summary>
        /// <param name="pageRequest">type check</param>
        /// <returns></returns>
        [HttpGet("targeted/zdcSysstdExportExcel")]
        [Produces("application/octet-stream")]
        //[ProducesResponseType(200)]
        //[ProducesResponseType(401)]
        public IActionResult zdcSysstdExportExcel([FromQuery] string startTime, [FromQuery] string endTime, [FromQuery] string roletype, [FromQuery] string xzqdm, [FromQuery] string towndm)
        {
            try
            {
                //var list = new List<VillageFireSafetyAttrDto>();
                var userNumber = this.CurrentUser();

                var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
                var sbSql = new StringBuilder();
                var sbSqlWhere = new StringBuilder();
                var sqlParams = new List<NpgsqlParameter>();

                var sbSqlBuildingEx = new StringBuilder();

                string district = user.District;

                var options = new JsonSerializerOptions();
                options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
                Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


                string strWhere = "  ";

                if (user.RoleType == 1 || user.RoleType == 2)
                {
                    strWhere += " and xzq!='东城区' and xzq!='西城区' ";
                }
                else if (user.RoleType == 3)  //区级
                {
                    strWhere += " and xzqdm='" + model.RegionCode + "'";
                }
                else if (user.RoleType == 4)
                {
                    string tdm = model.RegionCode;
                    if (model.RegionCode.Length != 12)
                    {
                        tdm = model.RegionCode + "000";
                    }
                    strWhere += " and towndm='" + tdm + "'";
                }
                else if (user.RoleType == 5)
                {
                    strWhere += " and villagedm='" + model.RegionCode + "'";
                }
                if (string.IsNullOrEmpty(startTime))
                {
                    startTime = "2024-01-01";
                    endTime = DateTime.Now.ToString("yyyy-MM-dd");
                }
                if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm))
                {
                    strWhere += " and xzqdm='" + xzqdm + "' and towndm='" + towndm + "'";
                }
                else if (!string.IsNullOrEmpty(xzqdm) && string.IsNullOrEmpty(towndm))
                {
                    strWhere += " and xzqdm='" + xzqdm + "'";
                }
                string table_baselog = "";
                string table_yhlog = "";
                string yhly = "";
                if (roletype == "2" || roletype == "3" || roletype == "4")
                {
                    table_baselog = "zm_pc_baselog_sqjz";
                    table_yhlog = "zm_pc_yhlog_sqjz";
                    yhly = roletype;
                }
                else if (roletype == "5")
                {
                    table_baselog = "zm_pc_baselog_cj";
                    table_yhlog = "zm_pc_yhlog_cj";
                }
                string today = DateTime.Now.ToString("yyyy-MM-dd");
                string table_fwzt = "";
                if (!string.IsNullOrEmpty(endTime))
                {
                    DateTime endData = Convert.ToDateTime(endTime);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                }
                else
                {
                    DateTime endData = Convert.ToDateTime(today);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                }
                string sql = "";
                if (roletype == "6")
                {
                    sql = "select ROW_NUMBER() OVER ()index,* from (select  z.xzq,z.townold town, z.village,COALESCE(zcount,0)zcount,COALESCE(zccount,0)zccount,case when  hy_pencent is null then '%' else hy_pencent end " +
                        "hy_pencent, COALESCE(ssltdbfjsl,0) ssltdbfjsl ,COALESCE(three,0) three,COALESCE(two,0) two,COALESCE(one,0) one ,COALESCE(three_ywccount,0) " +
                        "three_ywccount,COALESCE(two_ywccount,0) two_ywccount,COALESCE(one_ywccount,0) one_ywccount,COALESCE(first_no_count,0) first_no_count," +
                        "COALESCE(second_yes_count,0) second_yes_count,COALESCE(wwccount,0) wwccount,case when one_pencent is null then '%' else  one_pencent end one_pencent, " +
                        "case when two_pencent is null then '%' else two_pencent end two_pencent, case when three_pencent is null then '%' else three_pencent end three_pencent, " +
                        "case when wdbl_percent  is null then '%' else wdbl_percent end wdbl_percent, case when dbl_pencent is null then '%' else dbl_pencent end dbl_pencent, " +
                        "case when zgl_pencent is null then '%' else  zgl_pencent end zgl_pencent from zdcmd z left join (\r\n\tselect ROW_NUMBER() OVER()index ,* " +
                        "from(SELECT * FROM pro_std_cj_zc('"+endTime+" 23:59:59','"+table_fwzt+"') order by xzqdm) " +
                        "t where 1=1 and villagedm in(select villagedm from zdcmd)   and xzq!='东城区' and xzq!='西城区'  ) t on z.villagedm=t.villagedm order by t.xzqdm,z.villagedm) t";
                }
                else
                {
                    sql = "select ROW_NUMBER() OVER ()index,* from (select  t.xzqdm,z.xzq,z.townold town, z.village,COALESCE(zcount,0)zcount,COALESCE(zccount,0)zccount,case when  hy_pencent is null then '%' else hy_pencent end " +
                        "hy_pencent, COALESCE(ssltdbfjsl,0) ssltdbfjsl ,COALESCE(three,0) three,COALESCE(two,0) two,COALESCE(one,0) one ,COALESCE(three_ywccount,0) " +
                        "three_ywccount,COALESCE(two_ywccount,0) two_ywccount,COALESCE(one_ywccount,0) one_ywccount,COALESCE(first_no_count,0) first_no_count," +
                        "COALESCE(second_yes_count,0) second_yes_count,COALESCE(wwccount,0) wwccount,case when one_pencent is null then '%' else  one_pencent end one_pencent, " +
                        "case when two_pencent is null then '%' else two_pencent end two_pencent, case when three_pencent is null then '%' else three_pencent end three_pencent, " +
                        "case when wdbl_percent  is null then '%' else wdbl_percent end wdbl_percent, case when dbl_pencent is null then '%' else dbl_pencent end dbl_pencent, " +
                        "case when zgl_pencent is null then '%' else  zgl_pencent end zgl_pencent from zdcmd z left join (\r\n\tselect ROW_NUMBER() OVER()index ,* " +
                        "from(SELECT * FROM pro_std_cj('"+startTime+"', '"+endTime+" 23:59:59', '"+yhly+"','"+table_baselog+"','"+ table_yhlog + "','"+table_fwzt+"') order by xzqdm) " +
                        "t where 1=1 and villagedm in(select villagedm from zdcmd)   and xzq!='东城区' and xzq!='西城区'  ) t on z.villagedm=t.villagedm order by t.xzqdm,z.villagedm) t  where 1=1 "+strWhere;
                 }

                //写入缓存
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet(model.RegionName + "");
                // 第一行 标题
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("序号");
                row.CreateCell(1).SetCellValue("区名称");
                row.CreateCell(2).SetCellValue("乡镇");
                row.CreateCell(3).SetCellValue("村名称");
                row.CreateCell(4).SetCellValue("房屋总量");
                row.CreateCell(5).SetCellValue("已核验房屋");
                row.CreateCell(6).SetCellValue("核验进度");
                row.CreateCell(7).SetCellValue("合格房屋");
                row.CreateCell(8).SetCellValue("当前合格率");
                row.CreateCell(9).SetCellValue("发现不合格房屋");
                row.CreateCell(10).SetCellValue("已整改");
                row.CreateCell(11).SetCellValue("未整改");
                row.CreateCell(12).SetCellValue("整改率");
                row.CreateCell(13).SetCellValue("当前不合格率");
                row.CreateCell(14).SetCellValue("一层合格率");
                row.CreateCell(15).SetCellValue("二层合格率");
                row.CreateCell(16).SetCellValue("三层及以上合格率");
                
                // 第二行 写数据
                int i = 1;
                foreach (object obj in data)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    if (obj != null)
                    {

                        var properties = obj as IDictionary<string, object>;
                        row = sheet.CreateRow(i);
                        foreach (var kvp in properties)
                        {
                            var val = kvp.Value + "";
                            if (kvp.Key == "index")
                            {
                                row.CreateCell(0).SetCellValue(i);
                            }
                            else if (kvp.Key == "xzq")
                            {
                                row.CreateCell(1).SetCellValue(val);
                            }
                            else if (kvp.Key == "town")
                            {
                                row.CreateCell(2).SetCellValue(val);
                            }
                            else if (kvp.Key == "village")
                            {
                                row.CreateCell(3).SetCellValue(val);
                            }
                            else if (kvp.Key == "zcount")
                            {
                                row.CreateCell(4).SetCellValue(val);
                            }
                            else if (kvp.Key == "zccount")
                            {
                                row.CreateCell(5).SetCellValue(val);
                            }
                            else if (kvp.Key == "hy_pencent")
                            {
                                row.CreateCell(6).SetCellValue(val);
                            }
                            else if (kvp.Key == "ssltdbfjsl")
                            {
                                row.CreateCell(7).SetCellValue(val);
                            }
                            else if (kvp.Key == "dbl_pencent")
                            {
                                row.CreateCell(8).SetCellValue(val);
                            }
                            else if (kvp.Key == "first_no_count")
                            {
                                row.CreateCell(9).SetCellValue(val);
                            }
                            else if (kvp.Key == "second_yes_count")
                            {
                                row.CreateCell(10).SetCellValue(val);
                            }
                            else if (kvp.Key == "wwccount")
                            {
                                row.CreateCell(11).SetCellValue(val);
                            }
                            else if (kvp.Key == "zgl_pencent")
                            {
                                row.CreateCell(12).SetCellValue(val);
                            }
                            else if (kvp.Key == "wdbl_percent")
                            {
                                row.CreateCell(13).SetCellValue(val);
                            }
                            else if (kvp.Key == "one_pencent")
                            {
                                row.CreateCell(14).SetCellValue(val);
                            }
                            else if (kvp.Key == "two_pencent")
                            {
                                row.CreateCell(15).SetCellValue(val);
                            }
                            else if (kvp.Key == "three_pencent")
                            {
                                row.CreateCell(16).SetCellValue(val);
                            }
                        }

                        i++;
                    }
                }

                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "人员疏散通道建设情况（重点村）" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx");
            }
            catch (Exception ex)
            {
                throw;
            }

        }

        /// <summary>
        /// 导出双通道(达标或不达标)明细
        /// </summary>
        /// <param name="pageRequest">type check</param>
        /// <returns></returns>
        [HttpGet("targeted/sysstdExportHourseExcel")]
        [Produces("application/octet-stream")]
        //[ProducesResponseType(200)]
        //[ProducesResponseType(401)]
        public IActionResult sysstdExportHourseExcel([FromQuery] string startTime, [FromQuery] string endTime, [FromQuery] string roletype, [FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] string nickname, [FromQuery] string txtKeyWord, [FromQuery] string jsrysstd)
        {
            try
            {
                //var list = new List<VillageFireSafetyAttrDto>();
                var userNumber = this.CurrentUser();

                var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
                var sbSql = new StringBuilder();
                var sbSqlWhere = new StringBuilder();
                var sbSqlBuildingEx = new StringBuilder();
                string district = user.District;
                var options = new JsonSerializerOptions();
                options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
                Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);



                string strWhere = " and 1=1 ";
                string xzqWhere = "";
                string where = "";
                if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && !string.IsNullOrEmpty(villagedm))
                {
                    strWhere += " and t3.xzqdm='" + xzqdm + "' and t3.town='" + towndm + "' and t3.village='" + villagedm + "'";
                    xzqWhere = " and t1.xzqdm='" + xzqdm + "' and  t1.town='" + towndm + "' and  t1.village='" + villagedm + "'";
                    where = " and xzqdm='" + xzqdm + "' and (towndm='" + towndm + "' OR town='" + towndm + "') and and (villagedm='" + villagedm + "' OR village='" + villagedm + "')";
                }
                else if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
                {
                    strWhere += " and t3.xzqdm='" + xzqdm + "' and t3.town='" + towndm + "' ";
                    xzqWhere = " and t1.xzqdm='" + xzqdm + "' and t1.town='" + towndm + "' ";
                    where = " and xzqdm='" + xzqdm + "' and (towndm='" + towndm + "' OR town='" + towndm + "') ";
                }
                else if (!string.IsNullOrEmpty(xzqdm) && string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
                {
                    strWhere += " and t3.xzqdm='" + xzqdm + "' ";
                    xzqWhere = " and t1.xzqdm='" + xzqdm + "' ";
                    where = " and xzqdm='" + xzqdm + "' ";
                }
                if (!string.IsNullOrEmpty(txtKeyWord))
                {
                    where += " and (hzxm like '%" + txtKeyWord + "%' or fwbh like '%" + txtKeyWord + "%' or address like '%" + txtKeyWord + "%')";
                }
                if (!string.IsNullOrEmpty(nickname))
                {
                    where += " and (create_name like '%" + txtKeyWord + "%')";
                }
                DateTime start = Convert.ToDateTime(startTime);
                DateTime end = Convert.ToDateTime(endTime);
                string toDay = end.ToString("yyyy-MM-dd");
                string table_fwzt = "";
                if (!string.IsNullOrEmpty(endTime))
                {
                    DateTime endData = Convert.ToDateTime(endTime);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                }
                else
                {
                    DateTime endData = Convert.ToDateTime(toDay);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                }
                string sql = " ";
                if (roletype == "6")
                {
                    sql = @" select ROW_NUMBER() OVER ()index,* from (SELECT t3.* FROM ( SELECT DISTINCT ON (t1.fwbh) t1.ID,t1.xzq,t1.xzqdm,t1.villagedm,t1.towndm,t1.town, t1.address,t1.hzxm,t1.village," +
                     "   t1.cs, t1.jzscxyaqcksl,t1.jzxyssltsl,t1.sstdjsqk,t1.xggdjsbp, t1.fwbh,tdsfdb jsrysstd,t1.create_on  FROM zm_pc_baselog t1  WHERE create_on BETWEEN '" + startTime + "' AND  '" + endTime + " 23:59:59'  " + xzqWhere +
                    "     ORDER BY t1.fwbh, t1.create_on DESC ) t3" +
                   "  INNER JOIN zm_pc_yhlog m2 ON t3.ID = m2.base_id" +
                     " WHERE t3.create_on BETWEEN '" + startTime + "' AND  '" + endTime + " 23:59:59' " + strWhere + " and jsrysstd='" + jsrysstd + "'  " +
                      " AND t3.fwbh not in (select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) 	and (final_create_on = '" + endTime + " 23:59:59' ))  order by village)  t  where 1=1  " + where;

                }
                else if (roletype == "5")
                {
                    sql = @" select ROW_NUMBER() OVER ()index,* from (SELECT t3.* FROM ( SELECT DISTINCT ON (t1.fwbh) t1.ID,t1.xzq,t1.xzqdm,t1.villagedm,t1.towndm,t1.town, t1.address,t1.hzxm,t1.village," +
                      "   t1.cs, t1.jzscxyaqcksl,t1.jzxyssltsl,t1.sstdjsqk,t1.xggdjsbp, t1.fwbh,jsrysstd,t1.create_on,t1.create_name  FROM zm_pc_baselog_cj t1  WHERE create_on BETWEEN '" + startTime + "' AND  '" + endTime + " 23:59:59'  " + xzqWhere +
                     "     ORDER BY t1.fwbh, t1.create_on DESC ) t3" +
                    "  INNER JOIN zm_pc_yhlog_cj m2 ON t3.ID = m2.base_id" +
                      " WHERE t3.create_on BETWEEN '" + startTime + "' AND  '" + endTime + " 23:59:59' " + strWhere + " and jsrysstd='" + jsrysstd + "'  " +
                       " AND t3.fwbh not in (select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) 	and (final_create_on = '" + endTime + " 23:59:59' ))  order by village)  t  where 1=1  " + where;
                }
                else if (roletype == "2" || roletype == "3" || roletype == "4")
                {
                    if (jsrysstd == "ljbhg")
                    {
                        sql = "SELECT ROW_NUMBER( ) OVER ( ) index,* FROM\t(\t\tWITH building_first_second AS (    select t1.* from (SELECT        " +
                            "t1.ID,t1.xzq,t1.xzqdm,t1.villagedm,t1.towndm,t1.town, t1.address,t1.hzxm,t1.village, t1.cs, t1.jzscxyaqcksl,t1.jzxyssltsl,t1.xggdjsbp, " +
                            "t1.fwbh,jsrysstd,t1.create_on,t1.create_name,t1.yhly,  FIRST_VALUE(jsrysstd) OVER (PARTITION BY fwbh ORDER BY create_on desc) AS first_jsrysstd,     " +
                            "   LEAD(jsrysstd, 1) OVER (PARTITION BY fwbh ORDER BY create_on desc) AS second_jsrysstd    FROM        " +
                            " zm_pc_baselog_sqjz t1 where create_on BETWEEN '" + startTime + "' AND '" + endTime + " 23:59:59' and yhly='"+ roletype + "') t1    INNER JOIN     " +
                            "   zm_pc_yhlog_sqjz t2 ON t1.id = t2.base_id    WHERE  t1.create_on BETWEEN '" + startTime + "' AND '" + endTime + " 23:59:59' and yhly='" + roletype + "'    " +
                            "    AND t1.fwbh NOT IN (select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) 	and (final_create_on = '" + endTime + " 23:59:59' )      ))\t select * from (select ROW_NUMBER() OVER (PARTITION BY fwbh ORDER BY create_on DESC) AS row_num," +
                            " *FROM     building_first_second where ((first_jsrysstd='否') OR ( second_jsrysstd = '否' AND first_jsrysstd = '是'))) t" +
                            " where 1=1  " + where + " and row_num=1 order by create_on desc) T   WHERE  1 = 1   " +
                             where;
                    }
                    else if (jsrysstd == "zg")
                    {
                        sql = "SELECT ROW_NUMBER( ) OVER ( ) index,* FROM\t(\t\tWITH building_first_second AS (    select t1.* from (SELECT        " +
                           "t1.ID,t1.xzq,t1.xzqdm,t1.villagedm,t1.towndm,t1.town, t1.address,t1.hzxm,t1.village, t1.cs, t1.jzscxyaqcksl,t1.jzxyssltsl,t1.xggdjsbp, " +
                           "t1.fwbh,jsrysstd,t1.create_on,t1.create_name,t1.yhly,  FIRST_VALUE(jsrysstd) OVER (PARTITION BY fwbh ORDER BY create_on desc) AS first_jsrysstd,     " +
                           "   LEAD(jsrysstd, 1) OVER (PARTITION BY fwbh ORDER BY create_on desc) AS second_jsrysstd    FROM        " +
                           " zm_pc_baselog_sqjz t1 where create_on BETWEEN '" + startTime + "' AND '" + endTime + " 23:59:59' and yhly='"+ roletype + "') t1    INNER JOIN     " +
                           "   zm_pc_yhlog_sqjz t2 ON t1.id = t2.base_id    WHERE  t1.create_on BETWEEN '" + startTime + "' AND '" + endTime + " 23:59:59' and yhly='" + roletype + "'    " +
                           "    AND t1.fwbh NOT IN ( select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) 	and (final_create_on = '" + endTime + " 23:59:59' )  ))\t select * from (select ROW_NUMBER() OVER (PARTITION BY fwbh ORDER BY create_on DESC) AS row_num," +
                           " *FROM     building_first_second where (( second_jsrysstd = '否' AND first_jsrysstd = '是'))) t" +
                           " where 1=1  " + where + " and row_num=1 order by create_on desc) T   WHERE  1 = 1   " +
                            where;
                    }
                    else 
                    {
                        sql = @" select ROW_NUMBER() OVER ()index,* from (SELECT t3.* FROM ( SELECT DISTINCT ON (t1.fwbh) t1.ID,t1.xzq,t1.xzqdm,t1.villagedm,t1.towndm,t1.town, t1.address,t1.hzxm,t1.village," +
                  "   t1.cs, t1.jzscxyaqcksl,t1.jzxyssltsl,t1.sstdjsqk,t1.xggdjsbp, t1.fwbh,jsrysstd,t1.yhly,t1.create_on,t1.create_name  FROM zm_pc_baselog_sqjz t1  WHERE create_on BETWEEN '" + startTime + "' AND  '" + endTime + " 23:59:59'  and yhly='" + roletype + "' " + xzqWhere +
                 "     ORDER BY t1.fwbh, t1.create_on DESC ) t3" +
                "  INNER JOIN zm_pc_yhlog_sqjz m2 ON t3.ID = m2.base_id" +
                  " WHERE t3.create_on BETWEEN '" + startTime + "' AND  '" + endTime + " 23:59:59' " + strWhere + " and jsrysstd='" + jsrysstd + "' and t3.yhly='" + roletype + "' " +
                   " AND t3.fwbh not in ( select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) 	and (final_create_on = '" + endTime + " 23:59:59' ) )  order by village)  t  where 1=1  " + where;
                       
                    }
                }
                //写入缓存
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet(model.RegionName + "");
                // 第一行 标题
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("序号");
                row.CreateCell(1).SetCellValue("区名称");
                row.CreateCell(2).SetCellValue("乡镇名称");
                row.CreateCell(3).SetCellValue("村级名称");
                row.CreateCell(4).SetCellValue("户主姓名");
                row.CreateCell(5).SetCellValue("地址");
                row.CreateCell(6).SetCellValue("房屋编号");
                row.CreateCell(7).SetCellValue("房屋层数");
                row.CreateCell(8).SetCellValue("现有安全出口数量");
                row.CreateCell(9).SetCellValue("现有疏散楼梯数量");
                row.CreateCell(10).SetCellValue("是否满足”双通道“要求");
                row.CreateCell(11).SetCellValue("检查时间");
                row.CreateCell(12).SetCellValue("检查人员");
                // 第二行 写数据
                int i = 1;
                foreach (object obj in data)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    if (obj != null)
                    {

                        var properties = obj as IDictionary<string, object>;
                        row = sheet.CreateRow(i);
                        foreach (var kvp in properties)
                        {
                            var val = kvp.Value+"";
                            if (kvp.Key == "index")
                            {
                                row.CreateCell(0).SetCellValue(i);
                            }
                            else if (kvp.Key == "xzq")
                            {
                                row.CreateCell(1).SetCellValue(val);
                            }
                            else if (kvp.Key == "towndm")
                            {
                                row.CreateCell(2).SetCellValue(val);
                            }
                            else if (kvp.Key == "villagedm")
                            {
                                row.CreateCell(3).SetCellValue(val);
                            }
                            else if (kvp.Key == "hzxm")
                            {
                                row.CreateCell(4).SetCellValue(val);
                            }
                            else if (kvp.Key == "address")
                            {
                                row.CreateCell(5).SetCellValue(val);
                            }
                            else if (kvp.Key == "fwbh")
                            {
                                row.CreateCell(6).SetCellValue(val);
                            }
                            else if (kvp.Key == "cs")
                            {
                                row.CreateCell(7).SetCellValue(val);
                            }
                            else if (kvp.Key == "jzscxyaqcksl")
                            {
                                row.CreateCell(8).SetCellValue(val);
                            }
                            else if (kvp.Key == "jzxyssltsl")
                            {
                                row.CreateCell(9).SetCellValue(val);
                            }
                            else if (kvp.Key == "jsrysstd")
                            {
                                row.CreateCell(10).SetCellValue(val);
                            }
                            else if (kvp.Key == "create_on")
                            {
                                row.CreateCell(11).SetCellValue(val);
                            }
                            else if (kvp.Key == "create_name")
                            {
                                row.CreateCell(12).SetCellValue(val);
                            }
                        }

                        i++;
                    }
                }

                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "人员疏散通道建设明细" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx");
            }
            catch (Exception ex)
            {
                throw;
            }

        }



        /// <summary>
        /// 导出双通道统计表（重点村）
        /// </summary>
        /// <param name="pageRequest">type check</param>
        /// <returns></returns>
        [HttpGet("targeted/export/sysstdVillageListExport")]
        [Produces("application/octet-stream")]
        //[ProducesResponseType(200)]
        //[ProducesResponseType(401)]
        public IActionResult sysstdVillageListExport([FromQuery] string startTime, [FromQuery] string endTime)
        {
            try
            {

                var userNumber = this.CurrentUser();
                string strWhere = "";
                var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
                string district = user.District;
                var options = new JsonSerializerOptions();
                options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
                Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


                if (model != null)
                {
                    if (user.RoleType == 1 || user.RoleType == 2)
                    {
                        strWhere += "and  xzq!='东城区' and xzq!='西城区'";
                    }
                    else if (user.RoleType == 3) //区级
                    {
                        strWhere += " and xzqdm='" + model.RegionCode + "'";
                    }
                    else if (user.RoleType == 4) //区级
                    {
                        strWhere += " and  towndm='" + model.RegionCode + "'";
                    }
                }
                string today = DateTime.Now.ToString("yyyy-MM-dd");
                string sql = "";
                if (string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime))
                {
                    startTime = "2024-01-01";
                    endTime = DateTime.Now.ToString("yyyy-MM-dd");
                }
                string basetable = "zm_pc_baselog";
                string yhtable = "zm_pc_yhlog";
                if (startTime == "2024-01-01" && endTime == today)
                {
                    basetable = "zm_pc_base";
                    yhtable = "zm_pc_yh";
                }
                string table_fwzt = "";
                if (!string.IsNullOrEmpty(endTime))
                {
                    DateTime endData = Convert.ToDateTime(endTime);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                }
                else
                {
                    DateTime endData = Convert.ToDateTime(today);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                }
                sql = "select ROW_NUMBER() OVER ()index ,* from(select m2.villagedm,m2.xzq,m2.xzqdm,m2.towndm,m2.town,m2.village,COALESCE(zcount,0)zcount,COALESCE(jzrks,0)jzrks,COALESCE(zzrs,0)zzrs,COALESCE(fjsl,0) fjsl,COALESCE(jzfjs,0)jzfjs," +
                 "COALESCE(one,0)one,COALESCE(two,0)two,COALESCE(three,0)three from (select t1.villagedm,t2.xzq,t2.xzqdm,t2.towndm,t2.town,zcount,jzrks,zzrs,fjsl,jzfjs,one,two,three from zdcmd t1 left join" +
                 " ( select ROW_NUMBER() OVER ()index ,* from (select * from pro_hoursejbxx_cj('" + startTime + "', '" + endTime + " 23:59:59', '" + basetable + "', '" + yhtable + "','"+ table_fwzt + "') order by towndm) t  " +
                 "where 1=1  and villagedm in(select villagedm from  zdcmd)) t2 on t1.villagedm=t2.villagedm) m1,zm_building_ledger m2 where m1.villagedm=m2.villagedm ) t";

                //写入缓存
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet("重点村双疏散通道统计情况");
                // 第一行 标题
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("序号");
                row.CreateCell(1).SetCellValue("区名称");
                row.CreateCell(2).SetCellValue("乡镇");
                row.CreateCell(3).SetCellValue("村名称");
                row.CreateCell(4).SetCellValue("房屋总量");
                row.CreateCell(5).SetCellValue("已核验房屋");
                row.CreateCell(6).SetCellValue("核验进度");
                row.CreateCell(7).SetCellValue("合格房屋");
                row.CreateCell(8).SetCellValue("当前合格率");
                row.CreateCell(9).SetCellValue("发现不合格房屋");
                row.CreateCell(10).SetCellValue("已整改");
                row.CreateCell(11).SetCellValue("未整改");
                row.CreateCell(12).SetCellValue("整改率");
                row.CreateCell(13).SetCellValue("当前不合格率");
                row.CreateCell(14).SetCellValue("一层合格率");
                row.CreateCell(15).SetCellValue("二层合格率");
                row.CreateCell(16).SetCellValue("三层及以上合格率");
                // 第二行 写数据
                int i = 1;
                foreach (object obj in data)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    if (obj != null)
                    {

                        var properties = obj as IDictionary<string, object>;
                        row = sheet.CreateRow(i);
                        foreach (var kvp in properties)
                        {
                            var val = kvp.Value.ToString();
                            if (kvp.Key == "index")
                            {
                                row.CreateCell(0).SetCellValue(i);
                            }
                            else if (kvp.Key == "xzq")
                            {
                                row.CreateCell(1).SetCellValue(val);
                            }
                            else if (kvp.Key == "town")
                            {
                                row.CreateCell(2).SetCellValue(val);
                            }
                            else if (kvp.Key == "village")
                            {
                                row.CreateCell(3).SetCellValue(val);
                            }
                            else if (kvp.Key == "zcount")
                            {
                                row.CreateCell(4).SetCellValue(val);
                            }
                            else if (kvp.Key == "zccount")
                            {
                                row.CreateCell(5).SetCellValue(val);
                            }
                            else if (kvp.Key == "hy_pencent")
                            {
                                row.CreateCell(6).SetCellValue(val);
                            }
                            else if (kvp.Key == "ssltdbfjsl")
                            {
                                row.CreateCell(7).SetCellValue(val);
                            }
                            else if (kvp.Key == "dbl_pencent")
                            {
                                row.CreateCell(8).SetCellValue(val);
                            }
                            else if (kvp.Key == "first_no_count")
                            {
                                row.CreateCell(9).SetCellValue(val);
                            }
                            else if (kvp.Key == "second_yes_count")
                            {
                                row.CreateCell(10).SetCellValue(val);
                            }
                            else if (kvp.Key == "wwccount")
                            {
                                row.CreateCell(11).SetCellValue(val);
                            }
                            else if (kvp.Key == "zgl_pencent")
                            {
                                row.CreateCell(12).SetCellValue(val);
                            }
                            else if (kvp.Key == "wdbl_percent")
                            {
                                row.CreateCell(13).SetCellValue(val);
                            }
                            else if (kvp.Key == "one_pencent")
                            {
                                row.CreateCell(14).SetCellValue(val);
                            }
                            else if (kvp.Key == "two_pencent")
                            {
                                row.CreateCell(15).SetCellValue(val);
                            }
                            else if (kvp.Key == "three_pencent")
                            {
                                row.CreateCell(16).SetCellValue(val);
                            }

                        }

                        i++;
                    }
                }
                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "重点村双疏散通道统计情况" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx");
            }
            catch (Exception ex)
            {
                throw;
            }
        }
        /// <summary>
        /// 双通道————房屋预警
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("targeted/housingearlyList")]
        [ProducesResponseType(typeof(RespRet<Page<object>>), 200)]
        public RespRet<Page<object>> housingearlyList([FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] string cs, [FromQuery] string startTime, [FromQuery] string endTime, [FromQuery] int pageSize, [FromQuery] int pageindex,[FromQuery] string remark)
        {

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }

            string strWhere = " ";
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }
            string district = user.District;
            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


            if (model != null)
            {
                if (user.RoleType == 3) //区级
                {
                    strWhere += " and xzqdm='" + model.RegionCode + "'";
                }
            }
            if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && !string.IsNullOrEmpty(villagedm))
            {
                strWhere = " and xzqdm='" + xzqdm + "' and (towndm='" + towndm + "') and  (villagedm='" + villagedm + "')";
            }
            else if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
            {
                strWhere = " and xzqdm='" + xzqdm + "' and (towndm='" + towndm + "') ";
            }
            else if (!string.IsNullOrEmpty(xzqdm) && string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
            {
                strWhere = " and xzqdm='" + xzqdm + "' ";
            }
            if (!string.IsNullOrEmpty(cs)) 
            {
                if (cs.Contains("3"))
                {
                    string[] arrr = cs.Split(',');
                    if (arrr.Length==2)
                    {
                        string cssql = "";
                        for (int i = 0; i < arrr.Length; i++)
                        {
                            if (arrr[i] == "3")
                            {
                                cssql += "  cs >=3 OR ";
                            }
                            if (arrr[i] == "1")
                            {
                                cssql += "  cs=1 OR ";
                            }
                            if (arrr[i] == "2")
                            {
                                cssql += " cs =2 OR ";
                            }
                        }

                        strWhere += " and (" + (cssql.Substring(0, cssql.Length - 3))+")";
                    }
                    else
                    {
                        strWhere += " and cs >=" + cs;
                    }
                }
                else 
                {
                    strWhere += " and cs in("+cs+")";
                }
            }

            if (!string.IsNullOrEmpty(remark)) 
            {
                strWhere += " and verification_discrepancy ='"+ remark + "'";
            }
            string today = DateTime.Now.ToString("yyyy-MM-dd");
            string table_fwzt = "";
            if (!string.IsNullOrEmpty(endTime))
            {
                DateTime endData = Convert.ToDateTime(endTime);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            else
            {
                DateTime endData = Convert.ToDateTime(today);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            string sql = "select ROW_NUMBER() OVER ()index ,* from (select * from pro_housingearly('" + startTime+ "','"+ endTime + "','"+ table_fwzt + "') order by villagedm) t  where 1=1 "+strWhere + "   limit " + pageSize + " offset " + pageSize * (pageindex-1);
            //string sql = "select ROW_NUMBER() OVER ()index ,* from (select * from zm_hourse_alarm where ((qj_create_on BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59') OR (sj_create_on BETWEEN '" + startTime + "' AND '" + endTime + " 23:59:59'))  order by xzqdm) t  where 1=1 " + strWhere + "   limit " + pageSize + " offset " + pageSize * (pageindex - 1);


            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
         || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
         || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            string sql_count = @"select count(1)cnt from (select * from pro_housingearly('" + startTime + "','" + endTime + "','"+ table_fwzt + "') order by villagedm) t  where 1=1 " + strWhere;
            //string sql_count = "select count(1) cnt from (select * from zm_hourse_alarm where ((qj_create_on BETWEEN '" + startTime + "' AND '" + endTime + " 23:59:59') OR (sj_create_on BETWEEN '" + startTime + "' AND '" + endTime + " 23:59:59')) ) t  where 1=1 " + strWhere ;

            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
          || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
          || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            //执行语句
            var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);

            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = list, NextPageIndex = pageindex, PageTotal = count, Total = count });

        }

        /// <summary>
        /// 导出双通道统计表（重点村）
        /// </summary>
        /// <param name="pageRequest">type check</param>
        /// <returns></returns>
        [HttpGet("targeted/export/housingearlyListExport")]
        [Produces("application/octet-stream")]
        //[ProducesResponseType(200)]
        //[ProducesResponseType(401)]
        public IActionResult housingearlyListExport([FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] string startTime, [FromQuery] string endTime, [FromQuery] int pageSize, [FromQuery] string cs, [FromQuery] int pageindex,[FromQuery] string remark)
        {
            try
            {
                var userNumber = this.CurrentUser();
                string strWhere = "";
                var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
                string district = user.District;
                var options = new JsonSerializerOptions();
                options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
                Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


                if (model != null)
                {
                    if (user.RoleType == 3) //区级
                    {
                        strWhere += " and xzqdm='" + model.RegionCode + "'";
                    }
                }
                if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && !string.IsNullOrEmpty(villagedm))
                {
                    strWhere = " and xzqdm='" + xzqdm + "' and (towndm='" + towndm + "') and  (villagedm='" + villagedm + "')";
                }
                else if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
                {
                    strWhere = " and xzqdm='" + xzqdm + "' and (towndm='" + towndm + "') ";
                }
                else if (!string.IsNullOrEmpty(xzqdm) && string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
                {
                    strWhere = " and xzqdm='" + xzqdm + "' ";
                }
                if (!string.IsNullOrEmpty(cs))
                {
                    if (cs.Contains("3"))
                    {
                        string[] arrr = cs.Split(',');
                        if (arrr.Length == 2)
                        {
                            string cssql = "";
                            for (int k = 0; k < arrr.Length; k++)
                            {
                                if (arrr[k] == "3")
                                {
                                    cssql += "  cs >=3 OR ";
                                }
                                if (arrr[k] == "1")
                                {
                                    cssql += "  cs=1 OR ";
                                }
                                if (arrr[k] == "2")
                                {
                                    cssql += " cs =2 OR ";
                                }
                            }

                            strWhere += " and (" + (cssql.Substring(0, cssql.Length - 3)) + ")";
                        }
                        else
                        {
                            strWhere += " and cs >=" + cs;
                        }
                    }
                    else
                    {
                        strWhere += " and cs in(" + cs + ")";
                    }
                }
                if (!string.IsNullOrEmpty(remark))
                {
                    strWhere += " and verification_discrepancy ='" + remark + "'";
                }
                string today = DateTime.Now.ToString("yyyy-MM-dd");
                string table_fwzt = "";
                if (!string.IsNullOrEmpty(endTime))
                {
                    DateTime endData = Convert.ToDateTime(endTime);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                }
                else
                {
                    DateTime endData = Convert.ToDateTime(today);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                }
                string sql = "select ROW_NUMBER() OVER ()index ,* from (select * from pro_housingearly('" + startTime + "','" + endTime + "','"+ table_fwzt + "') order by villagedm) t  where 1=1 " + strWhere ;
              //  string sql = "select ROW_NUMBER() OVER ()index ,* from (select * from zm_hourse_alarm where (qj_create_on BETWEEN '"+startTime+"' AND '"+ endTime + " 23:59:59' and sj_create_on BETWEEN '"+startTime+"' AND '"+ endTime + " 23:59:59') ) t  where 1=1 " + strWhere ;


                //写入缓存
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet("房屋预警");
                // 第一行 标题
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("序号");
                row.CreateCell(1).SetCellValue("区名称");
                row.CreateCell(2).SetCellValue("乡镇");
                row.CreateCell(3).SetCellValue("村名称");
                row.CreateCell(4).SetCellValue("户主姓名");
                row.CreateCell(5).SetCellValue("地址");
                row.CreateCell(6).SetCellValue("房屋编号");
                row.CreateCell(7).SetCellValue("层数");
                row.CreateCell(8).SetCellValue("备注");
                // 第二行 写数据
                int i = 1;
                foreach (object obj in data)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    if (obj != null)
                    {

                        var properties = obj as IDictionary<string, object>;
                        row = sheet.CreateRow(i);
                        foreach (var kvp in properties)
                        {
                            var val = kvp.Value.ToString();
                            if (kvp.Key == "index")
                            {
                                row.CreateCell(0).SetCellValue(i);
                            }
                            else if (kvp.Key == "xzq")
                            {
                                row.CreateCell(1).SetCellValue(val);
                            }
                            else if (kvp.Key == "town")
                            {
                                row.CreateCell(2).SetCellValue(val);
                            }
                            else if (kvp.Key == "village")
                            {
                                row.CreateCell(3).SetCellValue(val);
                            }
                            else if (kvp.Key == "hzxm")
                            {
                                row.CreateCell(4).SetCellValue(val);
                            }
                            else if (kvp.Key == "address")
                            {
                                row.CreateCell(5).SetCellValue(val);
                            }
                            else if (kvp.Key == "fwbh")
                            {
                                row.CreateCell(6).SetCellValue(val);
                            }
                            else if (kvp.Key == "cs")
                            {
                                row.CreateCell(7).SetCellValue(val);
                            }
                            else if (kvp.Key == "verification_discrepancy")
                            {
                                row.CreateCell(8).SetCellValue(val);
                            }
                        }

                        i++;
                    }
                }
                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "房屋预警信息" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx");
            }
            catch (Exception ex)
            {
                throw;
            }
        }
    }
}
